Sort Text in a cell from A-Z

**SophiaBB** · 10-09-2012, 08:56 AM

Hi

I have a list of employes and I want to sort not by first name but by last name and I don't want to rewrite it all.

can you please help to sort by the last word in the cell?

thank you

**K m** · 10-09-2012, 09:03 AM

Assume the names are in column A starting with cell A1:
- Add a column to the right ie column B
- Add this formula to cell B1 and copy down: =RIGHT(A1,LEN(A1)-FIND(" ",A1))
- Column B now contains the last name
- Sort columns A and B using Column B as the sort by criteria

If you don't need it, after sorting you can delete column B

**SophiaBB** · 10-09-2012, 09:48 AM

it doesn't work , it displays an error in the formula

**K m** · 10-09-2012, 09:56 AM

It works for me. Here is a sample file

**SophiaBB** · 10-09-2012, 10:22 AM

OK I see the problem I have more than two names in a cell (4 or 5), and I just want the last one.

**shon** · 10-24-2012, 03:48 AM

Originally Posted by SophiaBB

OK I see the problem I have more than two names in a cell (4 or 5), and I just want the last one.

Following K m's instructions above, just change the formula to the below. Should work fine.

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