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

[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?

 

[Gary''s Student]

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.