| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel - Parse Name
I have to parse out names that are not consistent. The data comes in as:
Mary Smith John J Smith I want the out to be: Smith John J or Smith Mary. Can anyone help? -- Thanks! Chris |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Parse Name
Easy if we use two cells. For any name (or phrase) assume a set of words
separated by a single space. First find the location of the LAST space in the phrase. Then everything to the right of the last space goes first and everything to the left of the last space follows. In A1: James L Ravenswood In B1: =SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) In C1: =RIGHT(A1,LEN(A1)-B1) & " " & LEFT(A1,B1-1) So B1 shows: 8 and C1 shows: Ravenswood James L -- Gary''s Student - gsnu200773 "Chris" wrote: I have to parse out names that are not consistent. The data comes in as: Mary Smith John J Smith I want the out to be: Smith John J or Smith Mary. Can anyone help? -- Thanks! Chris |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Parse Name
On Sun, 16 Mar 2008 15:35:01 -0700, Chris
wrote: I have to parse out names that are not consistent. The data comes in as: Mary Smith John J Smith I want the out to be: Smith John J or Smith Mary. Can anyone help? =MID(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(T RIM(A1))," ",CHAR(1), LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))+1,255)&" "& LEFT(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(T RIM(A1))," ",CHAR(1), LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))-1) would do that. --ron |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Parse Name
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&"
"&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1) "Chris" wrote: I have to parse out names that are not consistent. The data comes in as: Mary Smith John J Smith I want the out to be: Smith John J or Smith Mary. Can anyone help? -- Thanks! Chris |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Parse Name
Gary's Student: Thanks! This worked great!!!
-- Thanks! Chris "Chris" wrote: I have to parse out names that are not consistent. The data comes in as: Mary Smith John J Smith I want the out to be: Smith John J or Smith Mary. Can anyone help? -- Thanks! Chris |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Parse Name
Thanks!
-- Thanks! Chris "Teethless mama" wrote: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&" "&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1) "Chris" wrote: I have to parse out names that are not consistent. The data comes in as: Mary Smith John J Smith I want the out to be: Smith John J or Smith Mary. Can anyone help? -- Thanks! Chris |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Parse Name
Thanks!
-- Thanks! Chris "Ron Rosenfeld" wrote: On Sun, 16 Mar 2008 15:35:01 -0700, Chris wrote: I have to parse out names that are not consistent. The data comes in as: Mary Smith John J Smith I want the out to be: Smith John J or Smith Mary. Can anyone help? =MID(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(T RIM(A1))," ",CHAR(1), LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))+1,255)&" "& LEFT(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(T RIM(A1))," ",CHAR(1), LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))-1) would do that. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Parse this string | Excel Discussion (Misc queries) | |||
| Parse from the Right | Excel Worksheet Functions | |||
| How do I parse columns? | Excel Worksheet Functions | |||
| Excel GURUs help. (How to make program parse more than 1 forumla within a cell) | Excel Worksheet Functions | |||
| Q: parse string | Excel Discussion (Misc queries) | |||
Linear Mode
