Having bothered others with ways of parsing names, I thought I'd at lease
contribute a simple solution for doing the complete job...
Assume you have names of the following types in column C
Mrs Dorothy Hannity
Dr P R Rogers
Dana Delany
Mr Bradley K Pitts
Type the following formulas into the specified cells:
O1=FIND(" ",C1)
Determines the location/existence of the blank following the Salutaton
or First Name
P1=FIND(" ",C1,FIND(" ",C1)+1)
Determines the location/existence of the blank following the First Name
or Middle Initial(MI)
Q1=FIND(" ",R1)
Determines the location/existence of the blank following the Middle
Initial in the next, adjacent cell
R1=IF(ISERROR(P1),RIGHT(C1,LEN(C1)-O1),RIGHT(C1,LEN(C1)-P1))
Defines the Last Name or MI/LN if there is a MI
S1=LEFT(C1,O1-1)
Creates the Salutation column
T1=IF(ISERROR(S1)," ",MID(C1,O1+1,P1-O1))
Creates the First Name column
U1=IF(FIND(" ",R1)=2,LEFT(R1,1),"")
Creates the MI column
V1=IF(U1<>"",MID(R1,FIND(" ",R1)+1,99),R1)
Creates the Last Name column
Fill the entries down and then copy the resulting values into another set of
columns.
It works for all variables except for names with more than one middle
initial.
