Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
parse text string
Let's say I have a bunch of names, such as:
John Doe Jane Doe Jim Doe and I want to re-arrange them as Doe, John Doe, Jane Doe, Jim I can figure out that a formula to do this is: =MID(A2,FIND(" ",A2,1)+1,LEN(A2)) & ", " & LEFT(A2,FIND(" ",A2)) etc. However, if the name is John M Doe the above returns M Doe, John which isn't too helpful. So is there a single formula I can use to parse names, whether they are First Last or First Middle Initial Last to return Last First or Last First Middle Initial? I'm also open to using VBA if that is a better solution. Thanks, Dave |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
parse text string
Assuming that *all* names are either format:
John Doe John M Doe =MID(A2&", "&A2,FIND(" ",A2)+IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))1,3,1),LEN(A2)+1) Returns: Doe, John Doe, John M Biff "Dave F" wrote in message ps.com... Let's say I have a bunch of names, such as: John Doe Jane Doe Jim Doe and I want to re-arrange them as Doe, John Doe, Jane Doe, Jim I can figure out that a formula to do this is: =MID(A2,FIND(" ",A2,1)+1,LEN(A2)) & ", " & LEFT(A2,FIND(" ",A2)) etc. However, if the name is John M Doe the above returns M Doe, John which isn't too helpful. So is there a single formula I can use to parse names, whether they are First Last or First Middle Initial Last to return Last First or Last First Middle Initial? I'm also open to using VBA if that is a better solution. Thanks, Dave |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
parse text string
Just in case the method you're using to view these groups causes a line wrap
problem: =MID(A2&", "&A2,FIND(" ",A2)+IF(LEN(A2)- LEN(SUBSTITUTE(A2," ",""))1,3,1), LEN(A2)+1) Biff "T. Valko" wrote in message ... Assuming that *all* names are either format: John Doe John M Doe =MID(A2&", "&A2,FIND(" ",A2)+IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))1,3,1),LEN(A2)+1) Returns: Doe, John Doe, John M Biff "Dave F" wrote in message ps.com... Let's say I have a bunch of names, such as: John Doe Jane Doe Jim Doe and I want to re-arrange them as Doe, John Doe, Jane Doe, Jim I can figure out that a formula to do this is: =MID(A2,FIND(" ",A2,1)+1,LEN(A2)) & ", " & LEFT(A2,FIND(" ",A2)) etc. However, if the name is John M Doe the above returns M Doe, John which isn't too helpful. So is there a single formula I can use to parse names, whether they are First Last or First Middle Initial Last to return Last First or Last First Middle Initial? I'm also open to using VBA if that is a better solution. Thanks, Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
parse text string
Dave F wrote...
Let's say I have a bunch of names, such as: .... However, if the name is John M Doe the above returns M Doe, John which isn't too helpful. So is there a single formula I can use to parse names, whether they are First Last or First Middle Initial Last to return Last First or Last First Middle Initial? .... So there'd either be just two or three words in each name and only the last word would be the last name? If so, =IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1, MID(A1,FIND(" ",A1)+1,256)&", "&LEFT(A1,FIND(" ",A1)-1), MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,256)&", "& LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1))) But that won't work for multiple word surnames like de la Hoya or Lloyd George. There's no simple formula that works for all names. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
parse text string
Not to mention the other oddities such as
John Jones III John Brown, Sr. or names with an honorific as John Smith, PhD "Harlan Grove" wrote: Dave F wrote... Let's say I have a bunch of names, such as: .... However, if the name is John M Doe the above returns M Doe, John which isn't too helpful. So is there a single formula I can use to parse names, whether they are First Last or First Middle Initial Last to return Last First or Last First Middle Initial? .... So there'd either be just two or three words in each name and only the last word would be the last name? If so, =IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1, MID(A1,FIND(" ",A1)+1,256)&", "&LEFT(A1,FIND(" ",A1)-1), MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,256)&", "& LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1))) But that won't work for multiple word surnames like de la Hoya or Lloyd George. There's no simple formula that works for all names. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parse this string | Excel Discussion (Misc queries) | |||
How to parse a string with a date? | Excel Worksheet Functions | |||
Word could not parse your query options into a valid sql string | Excel Discussion (Misc queries) | |||
Parse data where break is a first uppercase character in a string? | Excel Worksheet Functions | |||
Q: parse string | Excel Discussion (Misc queries) |