Move last names to front
 

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

Move last names to front
 

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

Move last names to front
 

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

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