Try this formula instead, in case you have trailing spaces:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A17)," ",REPT(" ",99)),99))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
With names in Column A, starting in A1, enter this formula in B1:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Copy down as needed, THEN,
while the cells in Column B are *still* selected,
Right click in the selection and choose "Copy",
Right click again and choose "Paste Special",
and click on "Values", then <OK.

What you just did was remove the formulas and left just the data behind.

Now, select both columns and sort on Column B.

You can delete Column B when finished.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"dannyboy8" wrote in message
...
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!