software

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?

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
HISquot;Rebquot; gt; wrote in message
...
gt; Using Win XP, Excel 2003. All updates installed.
gt;
gt; Unfortunately the full name is contained in one cell. for example, Cell A1
gt; contains: John P. Jones. The worksheet cannot be changed so setting up
gt; separate columns is not an option. Is there any way to sort the column by
gt; last name?
On Thu, 16 Mar 2006 20:40:19 -0800, Reb gt; wrote:

gt;Using Win XP, Excel 2003. All updates installed.
gt;
gt;Unfortunately the full name is contained in one cell. for example, Cell A1
gt;contains: John P. Jones. The worksheet cannot be changed so setting up
gt;separate columns is not an option. Is there any way to sort the column by
gt;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

On Thu, 16 Mar 2006 20:40:19 -0800, Reb gt; wrote:

gt;Using Win XP, Excel 2003. All updates installed.
gt;
gt;Unfortunately the full name is contained in one cell. for example, Cell A1
gt;contains: John P. Jones. The worksheet cannot be changed so setting up
gt;separate columns is not an option. Is there any way to sort the column by
gt;last name?To extract just the last name, assuming they are in the above format, you can
use a formula like:

=MID(quot; quot;amp;A1,FIND(CHAR(1),SUBSTITUTE(quot; quot;amp;A1,quot; quot;,CHAR(1),
LEN(quot; quot;amp;A1)-LEN(SUBSTITUTE(quot; quot;amp;A1,quot; quot;,quot;quot;)))) 1,255)

This will pick up the last word in the string. By prepending a lt;spacegt; 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