Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting a list of names by last name.
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting a list of names 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 HIS "Reb" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting a list of names by last name.
On Thu, 16 Mar 2006 20:40:19 -0800, Reb wrote:
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting a list of names by last name.
On Thu, 16 Mar 2006 20:40:19 -0800, Reb wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i want to only select one name from a list of the same names | Excel Discussion (Misc queries) | |||
looking up names in a list | Excel Worksheet Functions | |||
filter 400 names from list 1 from list 2 with 4000 names | Excel Worksheet Functions | |||
How do I print a list of worksheet tab names in a workbook | Excel Worksheet Functions | |||
Sorting for specific words/numbers in a list | Excel Worksheet Functions |