Excel: Sort alphabetically using the last word in a cell?

  • Thread starter Thread starter Brent M
  • Start date Start date
B

Brent M

I have a larg list of names that are formatted first name, sometimes a middle
name or initial, and last names. Can I sort this list alphabetically using
the last names without having to reformat each name?
 
The last name is always the word following the last space in the fullname.
This is true if a middle name is present or not.

Assume that the fullnames are in column A. In B1 enter:
=SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN("
"))) and copy down

In C1 enter:
=MID(A1,B1+1,256) and copy down. Here is an example:

James Ravenswood 6 Ravenswood
Charley K Watanabe 10 Watanabe

Finally sort cols A thru C by col C.
 
Back
Top