Hi,

This is bad database design. A field containing the prefix and the first
and last name should be broken into three or more fields. If later you need
to use the combination you can bring them back together easily with &, the
concatenation operator.

To break this field into three columns select all the names and choose Data,
Text to Columns, pick Delimited, click Next, choose Space, click Next, pick a
destination cell where you have available room for your three columns and
click Finish.

Now sort on Last Name, First Name or whatever.

Alternatively, if you know that all names have a prefix and a first and last
name then you could use

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,20)

to extract the last names to another column and sort on that column.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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!