Move last names to front
This works much better:
=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",99)),99)) & ", " &
TRIM(SUBSTITUTE(B6,TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",99)),99)),""))
"Gary''s Student" wrote:
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
--
Gary''s Student - gsnu200853
"John" wrote:
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
"Gary''s Student" wrote:
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
--
Gary''s Student - gsnu200853
"John" wrote:
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
|