Question by  Masheda (36)

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.


Answer by  pdash (63)

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.


Answer by  bleo (41)

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.

posted by Anonymous
Wow - Thanks, Bleo! I've been Googling all over the place and your formula was the first one to work!  add a comment

Answer by  GreenMonster (27)

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.


Answer by  gigo (1706)

There is no build in functionality to do that. But with the help of visual basic there is a way. You can go through every field from the back to the beginning and copy the letters, until you find the first space. Put this string into a new column.

You have 50 words left!