Re: how to sort by last word in a 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

On Tue, 5 Feb 2008 14:37:06 -0800, Teacher_Becky
<Teache...@discussions.microsoft.com> wrote:

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

[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