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

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

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

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

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

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

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