Excel: Sort alphabetically using the last word in a cell?

  • Thread starter Thread starter Brent M
  • Start date Start date
B

Brent M

I have a larg list of names that are formatted first name, sometimes a middle
name or initial, and last names. Can I sort this list alphabetically using
the last names without having to reformat each name?
 
The last name is always the word following the last space in the fullname.
This is true if a middle name is present or not.

Assume that the fullnames are in column A. In B1 enter:
=SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN("
"))) and copy down

In C1 enter:
=MID(A1,B1+1,256) and copy down. Here is an example:

James Ravenswood 6 Ravenswood
Charley K Watanabe 10 Watanabe

Finally sort cols A thru C by col C.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I separate first and last names into two columns? 2
Columns not sorting properly. 2
last name alphabetical order 3
Alphabetically list of names BOLD and NOT bold 13
Alphabetize in multiple columns 2
Highlight Sorted Alphabetical List 6
Open File menu sort alphabetical. 6
sorting multiple columns alphabetically 1

Back
Top