Move last names to front

J

John

I have a large sheet of rows of names and addresses that lists names in the
order:
firstname middlename lastname
What is a quick way to change all entries in the names column to
lastname, firstname middlename
so I can sort the list by last name.

I appreciate your help, -John
 
G

Gary''s Student

With James William Ravenswood in A1

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)) & ", " & LEFT(A1,FIND("
",A1,FIND(" ",A1,1)+1))

will display
Ravenswood, James William
 
J

John

This only works w/ 2 word names. Turns out I have serveral in list who have
multiple middle names. What I need to do is pick off the last name and move
it to the front followed by the comma and the remaining string w/ the last
name removed.

How do I extract the last word in a character string?

I appreciate your help, -John
 
G

Gary''s Student

In B1 enter:
=SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
In C1 enter:
=RIGHT(A1,LEN(A1)-B1) & ", " & LEFT(A1,B1-1)

If A1 contains:
Juan Carlos De Borbon Y Borbon
Then B1 displays:
24
and C1 displays:
Borbon, Juan Carlos De Borbon Y
 
J

John

This works much better:
=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",99)),99)) & ", " &
TRIM(SUBSTITUTE(B6,TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",99)),99)),""))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top