How to Sort by Last Name in Excel (5 Useful Methods)

If you work with datasets in Excel, you will often need to sort these datasets by name. Sometimes you may need to sort the dataset based on your last name. In this article, 5 Useful methods have been described to Sort by Last Name in Excel.

To illustrate the procedures, here is a sample dataset of 10 persons with Full Name in the Cell range B5:B14.

Now, let us follow the methods below to sort the names by their last names.

1. Using Find & Replace Feature in Excel to Extract and Sort by Last Name

In this first method, we will use the Find & Replace feature of Excel to split and sort the last names from the dataset. To do the task, follow the steps below.

First, copy column B by pressing Ctrl + C on your keyboard and paste it to column C by pressing Ctrl + V.

Then, select column C and press Ctrl+H to open the Find and Replace dialogue box.
Here, put an Asterisk (*) following a space in the Find what box.
Along with it, keep the Replace with box blank.
Next, click on Replace All > Close.

Finally, you will successfully extract the last names in the Cell range C5:C14.

Now, select both columns B and C and go to the Data tab to select the Sort icon from the Sort & Filter group.

Afterward, choose Sort by as Last Name and Order as A to Z.
Lastly, press OK.

Finally, the dataset including the full names appears alphabetically based on the last name.

Read More: How to Sort Duplicates in Excel

2. Applying Text to Columns Option for Sorting by Last Name in Excel

In this section, we will sort the last names using the Text to Columns method. Follow the steps below to perform the task.

First, select Data and press the Text to Columns option in the Data Tools group.

As a result, you will see the Convert Text to Column Wizard dialogue appear.
Here, select Delimited and click Next.

Afterward, select Space as the Delimiter and press Next.

Following, choose the Destination from your workbook and press Finish.

Finally, you will get the First and Last Names separately like this.

Read More: How to Sort by Name in Excel

3. Sort by Last Name in Excel Using Excel Formula

The third method is sorting the dataset by Last Name using a formula. The benefit of this method is that the result is dynamic compared to the other two methods described above because if I add more names to my list, I can simply copy and paste the formula into the cells of the column.

First, select the empty Cell C5 and type the formula there.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

Then, press Enter.
Following, the Last Name appears in the cell.

In this formula, the RIGHT function extracts the rightmost value from Cell B5. Then, the LEN function defines the length of the text string. Lastly, the FIND function looks for the required text.

Lastly, apply the AutoFill tool to get all the last names at once.

Moreover, if there is a middle name or a title before the name (such as Mr or Ms.), you need to use the below formula:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

The above formula finds the position of the last space character and then uses it to extract the last name.

Read More: How to Sort Merged Cells in Excel

4. Sort by Last Name Using the Excel Flash Fill Option

Another quick and fast method is the Flash Fill method. It helps manipulate the data by identifying patterns. For this to work, you need to fill in the expected result in the first cell to get the overall result.

First, type the last name of Cell B5 in Cell C5.
Then, pull down the cursor over the bottom-right part of the selection.

Next, the cursor changes to a plus icon.
Here, select the AutoFill option and choose Flash Fill.

Finally, the last names appear in column C.

Read More: How to Sort Unique List in Excel

5. Dynamically Sort by Last Name and Extract with Power Query

In this last method, we will use the Power Query tool in Excel to sort the last names dynamically. Let’s see how it works.

First, select the dataset with First Names and press Ctrl + T on your keyboard.
Then, mark checked the My table has headers option in the Create Table window and press OK.

As a result, you will get the dataset as a table like this.

Next, go to the Data tab and select From Table/Range under the Get & Transform Data section.

Accordingly, you will get the Power Query Editor window.
In this window, right-click on the first column and select Duplicate Column.

Then, click on Split Column in the Transform group.

In the drop-down menu, choose By Delimiter.

Afterward, you will get the Split Column by Delimiter window.
Here, keep the selections as shown in the image below and press OK.

Following, the last names will appear in a new column like this.

Now, right-click on the second column header and click on Remove to erase it.

After this, click on the header arrow of the Full Name- Copy.2 column and select Sort Ascending.

Lastly, go to the Home tab and select Close & Load To.

Therefore, select the location to put the data in the Import Data dialogue box and press OK.

Finally, you will get the names sorted by the last names beside the original dataset.

Additional Tips to Sort by Last Name in Excel

As the Flash Fill method works on identifying a pattern, in some cases this method may not work. If this problem arises repeat the expected result in one or two more cells.
Make sure that your original dataset is free from any unnecessary spaces. Otherwise, it will return to a blank cell.

Download Practice Workbook

Download this sample workbook to practice by yourself.

Sort by Last Name.xlsx

Conclusion

So these are 5 different ways that you can use to sort data by last name. We hope that you found this article useful. You can use any of these methods and share your thoughts with us. Feel free to explore more tutorials in our blog.