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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=498763