# 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.

Here's a description of the formulas shown above:

 Cell Formula Description Result B2 =FIND(" ",A2) Finds the position of the first space in "Elsie Carr" 6 C2 =MID("A2,B2+1,30) 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. Carr D2 =LEFT(A2,B2-1) 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." Elsie E2 =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.

Connect with an expert