Sort by last names

If you want to sort a list of names by last name, but they start with the first name (like "Elsie Carr"), you'll need to change them so they start with the last name (like "Carr, Elsie"). To do this, we'll use a few formulas.

This shows some formulas in B2, C2, and D2 that we'll bring together in cell E2.

Formulas that convert a full name to Last, First

Here's a description of the formulas shown above:






=FIND(" ",A2)

Finds the position of the first space in "Elsie Carr"




Finds the last 30 characters in "Elsie Carr" starting at the first position (7) after the space (this is the "C" in Carr). There's no problem with asking for a lot more characters than you need.




Finds the first name in "Elsie Carr" by returning the leftmost number of characters that are found before the first space (at position 6). So, the first 5 characters (position 6 minus 1) are returned—"Elsie."



=D2 & ", " & C2

Combines "Carr," a comma with a space (", "), and "Elsie" to return "Carr, Elsie."

Carr, Elsie

In E2, we've converted "Elsie Carr" to "Carr, Elsie." Now, drag the formula in E2 down through the cells below it to copy the formula and convert the rest of the names from column A.

Before you try to sort the names, you'll need to convert them from formula results to values:

  1. Select the cells starting with E2, and press Ctrl+C to copy them.

  2. On the Home tab, click the arrow under Paste, and click Paste Values.

Finally, to sort the names, select the cells and click Sort Ascending or Sort Descending.

Sort Ascending and Sort Descending

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!