Excel - Parse Name

C

Chris

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?
 
G

Gary''s Student

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
 
R

Ron Rosenfeld

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?
Click to expand...


=MID(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(TRIM(A1))," ",CHAR(1),
LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))+1,255)&" "&
LEFT(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(TRIM(A1))," ",CHAR(1),
LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))-1)

would do that.
--ron
 
T

Teethless mama

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&"
"&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)
 
C

Chris

Thanks!
--
Thanks!
Chris


Teethless mama said:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&"
"&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)
Click to expand...
 
C

Chris

Thanks!

--
Thanks!
Chris


Ron Rosenfeld said:
=MID(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(TRIM(A1))," ",CHAR(1),
LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))+1,255)&" "&
LEFT(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(TRIM(A1))," ",CHAR(1),
LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))-1)

would do that.
--ron
Click to expand...
 
Top