Again, probably an easier way but this worked.


=IF(ISERROR(PROPER(LEFT(A3,FIND("
",A3)))),PROPER(LEFT(A3,LEN(A3))),PROPER(LEFT(A3,F IND("
",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(LEF T(A3,FIND("
",A3)))&VLOOKUP(RIGHT(A3,LEN(A3)-FIND(" ",A3,1)),$J$1:$K$11,2,FALSE))

Steve


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