Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

Download this sample workbook to practice by yourself.


5 Useful Methods 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.

How to Sort by Last Name in Excel


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

1. Use Find & Replace Feature 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.

Use Find & Replace Feature to Extract and Sort by Last Name

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

Use Find & Replace Feature to Extract and Sort by Last Name

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

Use Find & Replace Feature to Extract and Sort by Last Name

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


2. Apply 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.

Apply Text to Columns Option for Sorting by Last Name in Excel

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

  • Afterward, select Space as the Delimiter and press Next.

Apply Text to Columns Option for Sorting by Last Name in Excel

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

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


3. Insert Formula to Sort by Last Name in Excel

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

Insert Formula to Sort by Last Name in Excel

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


4. Use Flash Fill Option to Sort by Last Name

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.

Use Flash Fill Option to Sort by Last Name

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

Use Flash Fill Option to Sort by Last Name

  • Finally, the last names appear in column C.


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.

Dynamically Sort by Last Name and Extract with Power Query

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

Dynamically Sort by Last Name and Extract with Power Query

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

Dynamically Sort by Last Name and Extract with Power Query

  • 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

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

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


Further Readings

Ratul Khan

Ratul Khan

Hello! Here is my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and I am very interested in research.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo