Sorting Cells in Alphabetical Order by Last Name

[Guest]

I have a file that was sent to me and the person wants to sort it by last
name. The problem is that the names were typed in one cell, for example "Ken
T. Jones" or sometimes "Kenneth Jones" - is there a way in Excel 2003 that
you can tell it to sort by the last name in a cell?

 

[Guest]

Say the names are in column A. In a helper column enter:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

and copy down. This yield the last name. Sort both columns by the helper
column.