software

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.--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=498763
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(quot; quot;,A1)))amp;VLOOKUP(RIGHT(A1,LEN(A1)-FIND(quot;
quot;,A1,1)),J1:K11,2,FALSE)

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=498763
This works when there is a suffix but if the field has no suffix it
returns #VALUE!

For example:

quot;SMITHquot; returns #VALUE!
quot;SMITH JRquot; returns quot;Smith Jrquot;

What do I need to add to this so that it will recognize names without a
suffix--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=498763
Again, probably an easier way but this worked.=IF(ISERROR(PROPER(LEFT(A3,FIND(quot;
quot;,A3)))),PROPER(LEFT(A3,LEN(A3))),PROPER(LEFT(A3,F IND(quot;
quot;,A3)))amp;VLOOKUP(RIGHT(A3,LEN(A3)-FIND(quot; quot;,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(quot;
quot;,TRIM(A3))))),PROPER(LEFT(A3,LEN(A3))),PROPER(LEF T(A3,FIND(quot;
quot;,A3)))amp;VLOOKUP(RIGHT(A3,LEN(A3)-FIND(quot; quot;,A3,1)),$J$1:$K$11,2,FALSE))

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=498763
Thank you very much--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=498763