Another casing question

[jermsalerms]

cell C2 imports Last name and suffix in all caps. I need a formula for
cell c3 that will convert it to proper casing

Cell 2 Example:

SMITH JR
SMITH SR
SMITH II
SMITH III
SMITH IV
SMITH MD
SMITH PHD

Cell 3 need to convert to:
Smith Jr
Smith Sr
Smith II
Smith III
Smith IV
Smith MD
Smith PhD

I can't use the funtion PROPER by itself because it converts II to
Ii...etc.

 

[SteveG]

Not sure if this is the easiest way but it seemed to work for me. I
created a cross-reference table in my sheet for each suffix. Column 1
contains the current imported formats. Column 2 contains what I want
it to be.

Column 1 (J)
JR
SR
IV
MD
PHD

Column 2 (K)
Jr
Sr
IV
MD
PhD

I then entered this formula where I wanted to do my conversion.

=PROPER(LEFT(A1,FIND(" ",A1)))&VLOOKUP(RIGHT(A1,LEN(A1)-FIND("
",A1,1)),J1:K11,2,FALSE)

HTH

Steve

 

[jermsalerms]

This works when there is a suffix but if the field has no suffix it
returns #VALUE!

For example:

"SMITH" returns #VALUE!
"SMITH JR" returns "Smith Jr"

What do I need to add to this so that it will recognize names without a
suffix

 

[SteveG]

Again, probably an easier way but this worked.


=IF(ISERROR(PROPER(LEFT(A3,FIND("
",A3)))),PROPER(LEFT(A3,LEN(A3))),PROPER(LEFT(A3,FIND("
",A3)))&VLOOKUP(RIGHT(A3,LEN(A3)-FIND(" ",A3,1)),$J$1:$K$11,2,FALSE))

One issue with this is if there is a space at the end of the last name
but no suffix, it will return the #N/A error. Use the TRIM function to
avoid this.

=IF(ISERROR(PROPER(LEFT(TRIM(A3),FIND("
",TRIM(A3))))),PROPER(LEFT(A3,LEN(A3))),PROPER(LEFT(A3,FIND("
",A3)))&VLOOKUP(RIGHT(A3,LEN(A3)-FIND(" ",A3,1)),$J$1:$K$11,2,FALSE))

Steve

 

[jermsalerms]

Thank you very much