> It works for all variables except for names with more than one middle
> initial.
Jr., Sr., III, Esq., Phd, MBA, CEO....
"Jim Berglund" <(E-Mail Removed)> wrote in message
news:811E3DD6-D1FF-4904-841C-(E-Mail Removed)...
> 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.
>
|
Similar Threads
