How do you sort in 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.

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.