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.
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.
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.