Reversing First and Last Names
 

I am extracting one single column from word where the person's name is
written "Mr. John Smith." Is there any way in excel to have it appear as
"Smith, Mr. John" It can be in the same column but I need to be able to sort
by the last name - not the title!

You could extract the first letter of the last word with:

=MID(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))), 1)="
",ROW(INDIRECT("1:"&LEN(A1)))))+1,1)

and then sort based on that. The formula above is an array formula, so be
sure to press <ctrl<shift<enter after inserting the formula. Then fill
down.

HTH
Jason
Atlanta, GA



"excelnewbie" wrote:

I am extracting one single column from word where the person's name is
written "Mr. John Smith." Is there any way in excel to have it appear as
"Smith, Mr. John" It can be in the same column but I need to be able to sort
by the last name - not the title!

Assuming that your data is in column A and is all of the form title, space,
first_name, space, last_name, then use Data Text to Columns to fill column
B with the title, fill column C with the first_name, and fill column D with
the last_name.

Enter
=D1&", "&B1&" "&C1
in cell E1 and copy cown
--
Gary's Student


"excelnewbie" wrote:

I am extracting one single column from word where the person's name is
written "Mr. John Smith." Is there any way in excel to have it appear as
"Smith, Mr. John" It can be in the same column but I need to be able to sort
by the last name - not the title!

Always wanted to know how to do that...it is very useful....thanks a bunch
Gary's student....works like a charm

"Gary's Student" wrote:

Assuming that your data is in column A and is all of the form title, space,
first_name, space, last_name, then use Data Text to Columns to fill column
B with the title, fill column C with the first_name, and fill column D with
the last_name.

Enter
=D1&", "&B1&" "&C1
in cell E1 and copy cown
--
Gary's Student


"excelnewbie" wrote:

I am extracting one single column from word where the person's name is
written "Mr. John Smith." Is there any way in excel to have it appear as
"Smith, Mr. John" It can be in the same column but I need to be able to sort
by the last name - not the title!

If your name is in cell A1, then use:

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

Source:
http://www.mvps.org/dmcritchie/excel/strings.htm


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378050

THANKS to both of you - very helpful