Reference a directory to automatically format text

[Dominick11]

I'm trying to setup a directory of formulas that i could reference automatically if possible to format my text. I've attached a couple screenshots as an example. The 2nd screenshot shows an example of a formula I use to manually correct capitalization for a 3 letter design.

On the main sheet
Column A is where I would paste all of the name / initial text that needs to be formatted.
Column B & C is where the updated name / initials would appear with the correct format.
Column D would specify what type of format the name / initials are.

Row 2 is a "3 Letter" format where the first letter is lowercase, 2nd letter is uppercase, & 3rd letter is lowercase. This would appear in Row 2 Column B
Row 3 is a "Name Only" format where the first letter of each name needs to be capitalized. This would appear in Row 3 Column B
Row 4 is a "First & Last Initial" format where the first name needs to be capitalized in Row 3 Column B & the first letter of the last name needs to be capitalized in Row 3 Column C.

The directory sheet would have 2 columns
Column A - Shows the different type of text formats
Column B - is the formula for that text format

I appreciate any help.

Screen Shot 2020-08-18 at 12.22.38 PM.png
Screen Shot 2020-08-18 at 12.22.15 PM.png

[Bernie Deitrick]

You could just use nested formulas, chosen by IF:

=IF(D2="3 Letter",LOWER(LEFT(A2,1))& UPPER(MID(A2,2,1)) & LOWER(MID(A2,3,1)),IF(D2="Name Only",PROPER(A2),IF(D2="First & Last Initial",LEFT(PROPER(A2),FIND(" ",A2)+1),"")))

[Dominick11]

You could just use nested formulas, chosen by IF:

=IF(D2="3 Letter",LOWER(LEFT(A2,1))& UPPER(MID(A2,2,1)) & LOWER(MID(A2,3,1)),IF(D2="Name Only",PROPER(A2),IF(D2="First & Last Initial",LEFT(PROPER(A2),FIND(" ",A2)+1),"")))

Thank you this is great. I do have a question, for the "First & Last Initial" format, how do I get it to paste the last name initial in column c instead of in column b? Attached is a screenshot of how it's currently showing. Thanks again
Attachment 691609

[Bernie Deitrick]

Change to this

=IF(D2="3 Letter",LOWER(LEFT(A2,1))& UPPER(MID(A2,2,1)) & LOWER(MID(A2,3,1)),IF(D2="Name Only",PROPER(A2),IF(D2="First & Last Initial",LEFT(PROPER(A2),FIND(" ",A2)-1),"")))

and in C use this:

=IF(D2="First & Last Initial",MID(UPPER(A2),FIND(" ",A2)+1,1),"")

[Dominick11]

Thank you very much for your help. This will save me so much time.

[Bernie Deitrick]

That's great! Thanks for letting me now that you were able to solve your issues.

[Dominick11]

I have one last question. How could I expand on this formula: =IF(D2="First & Last Initial",MID(UPPER(A2),FIND(" ",A2)+1,1),"") to also include "First & Last Name" where it would copy the entire last name to column c.