Another Method for Parsing Names
 

It works for all variables except for names with more than one middle
initial.

Jr., Sr., III, Esq., Phd, MBA, CEO....





"Jim Berglund" wrote in message
...
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.

Another Method for Parsing Names
 

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.