Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing names from one column into 3. | Excel Programming | |||
parsing full names in to 3 columns | Excel Programming | |||
Formulas for Parsing Full names | Excel Worksheet Functions | |||
TypeLib Information Problem? Pass a Function's parameter names as string for parsing? | Excel Programming | |||
Parsing Full Names of varying lenths and parts | Excel Programming |