Hi,

Doesn't work on some cells isn't a very helpful description of the
problem.The only reason I can think of is may you have a Tilde (~) character
in some cells in which case it won't work. You can change the tilde in the
formula to (say) the @ sign or another more commonly used is the caret ^. I
Forgot it will also fail if there are no spaces in the cell.

Mike

"dannyboy8" wrote:

Thanks Mike, strangely, it works on some cells and not others, wonder why
that would be?

"Mike H" wrote:

Hi,

You need a helper column to extract the last name with this formula

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

having extracted the last names in the new column sort your data on this
last name.

Mike

"dannyboy8" wrote:

if I have names formatted by Mr and Mrs John Doe all in the same column, is
thee a way to sort by last name in excel 2007? the only sort I can figure out
is alpha by 1st letter, so it begins sorting the list by the 1st letter of
the 1st names. Thanks!