Excel - Parse Name

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

 

[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

 

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

=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

 

[Teethless mama]

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

 

[Chris]

Gary's Student: Thanks! This worked great!!!

 

[Chris]

Thanks!
--
Thanks!
Chris

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

 

[Chris]

Thanks!

--
Thanks!
Chris

=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