Sorting a list of names by last name.

[Guest]

Using Win XP, Excel 2003. All updates installed.

Unfortunately the full name is contained in one cell. for example, Cell A1
contains: John P. Jones. The worksheet cannot be changed so setting up
separate columns is not an option. Is there any way to sort the column by
last name?

 

[Nick Hodge]

Reb

You cannot sort by certain characters in a string via the UI. You will need
to seperate the last name into another column

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Ron Rosenfeld]

Using Win XP, Excel 2003. All updates installed.

Unfortunately the full name is contained in one cell. for example, Cell A1
contains: John P. Jones. The worksheet cannot be changed so setting up
separate columns is not an option. Is there any way to sort the column by
last name?

You will need to set up a helper column that contains only the last name; then
use that helper column as the sort key. You can subsequently delete or hide
the helper column.


--ron

 

[Ron Rosenfeld]

Using Win XP, Excel 2003. All updates installed.

Unfortunately the full name is contained in one cell. for example, Cell A1
contains: John P. Jones. The worksheet cannot be changed so setting up
separate columns is not an option. Is there any way to sort the column by
last name?

To extract just the last name, assuming they are in the above format, you can
use a formula like:

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

This will pick up the last word in the string. By prepending a <space> to the
string as I did, it will not give an error message if there is only a single
name in the string -- it will return that name.


--ron