Snippets home page
 
 
register
 
 
software

How do you sort in excel by the last word in the cell?

I would like to sort excel by the last word in the cell.

asked by Masheda (23)

You have 50 words left!
 
 
related questions

How can I collapse rows in excel?

How can I copy text from an excel cell?

How can I track my location through my cell phone?

How do I take one cell's entry, in Excel, and enter it into another cell?

How do you make a new line character in a Microsoft Excel cell?

How do you sort by dates in excel?

What is the excel function that will count the instances of a word?

Tags:  how to  software

answers

You cannot do that level of custom sort even in Excel 2007. You need to write VBA macro which will split the cell value and do a sort based on the last splitted value.

reply
posted by pdash (63)
7 votes

Assuming spaces separate words then copy the following formula into a column and cell adjacent to the sorting cell: =MID(SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,256). Copy and paste the values of this column and thensort on this column.

reply
posted by bleo (37)
  • Wow - Thanks, Bleo! I've been Googling all over the place and your formula was the first one to work!
    posted by Anonymous
7 votes

Let's say your data is in cells A1 to A10. In B1, use this (ugly! ) formula: =right(a1,len(a1)-find("*",substitute(a1," ","*",len(a1)-len(substitute(a1," ",""))))) and copy it to b1:b10. That extracts the last words. Now copy B1:B10 and paste it in place as values, then sort A1:B10 using Column B.

7 votes

Report a post on this page as inappropriate
 If you have questions about user content, read our liability disclaimer

  
   
Add your own answer in 50 words or less! Add your comment in 50 words or less!

copyright © 2008
conjecture corporation

FAQ About Us Contact Us Terms & Conditions Privacy Policy