How to Sort by Last Name in Excel

In Excel, if you have a column with full names, you 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

1. First, copy and paste the names to column B, and name it “Last Name”.
   
2. Now only select the last name column and use the keyboard shortcut (Ctrl + H) to open the find and replace option.
   
3. After that, only enter “*” an asterisk with space followed in the “Find what:” input bar.
   
4. Next, replace all the values by clicking the “Replace All” button. With this, it will remove all first names from the column.
   
5. From here, select the entire data and use the keyboard shortcut (Alt > A > S > S) to open the SORT dialog box.
   
6. Now, from the “Sort by” drop down, select the “Last Name” column.
   
7. 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 and paste a duplicate column with your names in the original column. And then, the name is as a “Last Name”.

1. Once you do that, select the column and go to Data > Data Tools > Text to Column.
   
2. And once you click on it, you get the dialog box to select the delimiter to split the values which you have in the column. So, click on “Next”.
   
3. Now from here, tick mark the “Space” and un-tick everything else. And after that, click on the “Next” button.
   
4. From here, you need to click on the finish to apply the text column and split to column values into two columns.
   
5. Next, name both columns, First and Last Names.
   
6. 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.

Get Last Name Column with Flash Fill to Sort

Besides the above methods, you can use the flash fill to create a new column with the last name.

1. First, create a new column with the name “Last Name”.
   
2. After that, for the first entry, enter the last name manually.
   
3. Now, go to the next cell and use the keyboard shortcut “Ctrl + E” to get the last names in the entire column.
   
4. Next, select the entire data and then use the keyboard shortcut (Alt > A > S > S) to the SORT dialog box.
   
5. 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 your last name in the last name column.

Use a Formula to Get Names for Sorting

With the below formula, you can get the last name from the name where you have the first and the last name.

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)))

Now, let’s continue to our example and open the SORT dialog box from the (Data > Sort), or use the keyboard shortcut key (Alt > A > S > S).

In the end, click OK to sort the data.