Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by name
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by name
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by name
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by name
Hi,
Doesn't work on some cells isn't a very helpful description of the problem.The only reason I can think of is may you have a Tilde (~) character in some cells in which case it won't work. You can change the tilde in the formula to (say) the @ sign or another more commonly used is the caret ^. I Forgot it will also fail if there are no spaces in the cell. Mike "dannyboy8" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by name
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by name
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting by name
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Values Without Sorting Formulas | Excel Discussion (Misc queries) | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
SORTING | Excel Discussion (Misc queries) | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) |