Sorting column by last word (name)

Hi there,

Put the following formula (assuming the data starts at row 2 - change as required) in the first available blank column next your data fill down as necessary and then sort the entire data range by it. You can then delete the column if you wish:

=RIGHT(SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),LEN(SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND("~",(SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

Note this formula was created by "Datsmart" from this thread.

HTH

Robert

how does that formula deal with da vinci which is a surname but with a gap in it?, the code only returns vinci