how to sort by last word in a cell?

[Teacher_Becky]

I have had a data base given to me with two columns: one of books and another
of authors (3500!). Each authors' whole name appears in one cell. There is
anywhere from one to four words per cell. I just want it to sort by the last
name of the author. How can I sort by the last word in the cell?

 

[Gord Dibben]

Assuming authors names are in Column A starting at A1 and each word is
space-separated.

Insert a blank column to the right of A

In B1 enter this formula

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))


Double-click on the fill handle to copy down.

Copy column B and edot>paste special(in place)>values>ok>esc.

Sort on that column.


Gord Dibben MS Excel MVP

 

[Jim Cone]

Create a helper column that contains the last name.
If the names start in cell B5 then this formula in C5 and filled down will display the last name...
=RIGHT(B5,LEN(B5)-FIND("<^>",SUBSTITUTE(B5," ","<^>",LEN($B$5)-LEN(SUBSTITUTE($B$5," ",""))),1))
(watch for word wrap - the formula should be one line)

Copy and paste values over the formulas then select all your data including the
helper column and sort with the helper column as the key column.

There are also commercial alternatives that can sort by last name.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - two dozen ways to sort with "Special Sort")




"Teacher_Becky"
wrote in message
I have had a data base given to me with two columns: one of books and another
of authors (3500!). Each authors' whole name appears in one cell. There is
anywhere from one to four words per cell. I just want it to sort by the last
name of the author. How can I sort by the last word in the cell?