In Excel, if you have a column with full names, you may want to sort those names based on the last name. In this situation, we can use different methods, and in this tutorial, we will discuss all these methods in detail.
Sort by the Last Name using Find and Replace
- First, copy and paste the names to column B, and name it “Last Name”.
- Now, select only the last name column and use the keyboard shortcut (Ctrl + H) to open the Find and Replace option.
- After that, enter an asterisk followed by a space (“* “) in the “Find what:” box and leave “Replace with:” empty.
- Next, replace all the values by clicking the “Replace All” button. With this, it will remove everything up to the last space (the first and middle names) from the column.
- From here, select the entire data and use the keyboard shortcut (Alt > A > S > S) to open the SORT dialog box.
- Now, from the “Sort by” drop down, select the “Last Name” column.
- In the end, click OK to sort the data.
Once you sort the data, you can delete the column you created with the last name.
Using Text to Column to Sort with Last Name
In this method, you also need to copy the names into a new column next to the original column, and then name that column “Last Name”.
- Once you do that, select the column and go to Data > Data Tools > Text to Columns.
- And once you click on it, you get the dialog box to split the values in the column. Keep “Delimited” selected and click on “Next”.
- Now, from here, tick “Space” and untick everything else. And after that, click on the “Next” button.
- From here, click on Finish to split the column values into two columns.
- Next, name both columns: First Name and Last Name.
- Now, select the entire data and open the sorting dialog box using the keyboard shortcut (Alt > A > S > S). And from there, select “Last Name”, and click OK to apply the sorting.
When you click OK, it sorts the entire data based on the “Last Name” column. Keep in mind that Text to Columns splits at every space, so a name with a middle name goes into three columns, and the cells to the right get overwritten. Use the Find and Replace or formula method for names like that.
Get Last Name Column with Flash Fill to Sort
Besides the above methods, you can use Flash Fill to create a new column with the last name.
- First, create a new column with the name “Last Name”.
- After that, for the first entry, enter the last name manually.
- Now, go to the next cell and use the keyboard shortcut “Ctrl + E” to get the last names in the entire column.
- Next, select the entire data and then use the keyboard shortcut (Alt > A > S > S) to open the SORT dialog box.
- From here, select the “Last Name” from the “Sort by” drop-down and then click on OK to sort the data.
Once you click OK, it sorts the data based on the last names in the Last Name column.
Use a Formula to Get Names for Sorting
With the below formula, you can get the last name from a name that has only a first and a last name:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
Or you can use this formula if you have First, Middle, and Last names in a cell.
=RIGHT(SUBSTITUTE(A2," ","-",2),LEN(SUBSTITUTE(A2," ","-",2))-FIND("-",SUBSTITUTE(A2," ","-",2)))
This formula needs exactly three names: with only two, it returns #VALUE!. If your list mixes names with and without a middle name, use the following formula instead. It works with any number of names, as it always takes the text after the last space:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
And in Microsoft 365 and Excel 2024, TEXTAFTER does the same with -1 as the instance number, which means the last space:
=TEXTAFTER(A2," ",-1)
Now, let’s continue with our example and open the SORT dialog box from Data > Sort, or use the keyboard shortcut (Alt > A > S > S).
In the end, click OK to sort the data.
Sort by Last Name with the SORTBY Function
In Microsoft 365 and Excel 2024, you can sort the names by last name with one formula and no helper column. SORTBY sorts the names in A2:A20 by the last names that TEXTAFTER returns:
=SORTBY(A2:A20,TEXTAFTER(A2:A20," ",-1),1)
Frequently Asked Questions
How do I sort by last name and then first name?
In the Sort dialog box, sort by the Last Name column, click Add Level, and then sort by the column with the full name (or the first name). People with the same last name are then sorted by their first name.
What if the names are written as “Last, First”?
Then the last name already comes first, so you can sort the column directly from A to Z without a helper column.
Why do some names sort in the wrong place?
Extra spaces are the usual reason, especially a space at the end of a name, which leaves the last name column blank. Clean the names with TRIM before you extract the last names.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.