How to Sort by Last Name in Excel (4 Methods)

How to Sort by Last Name in Excel (4 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, some methods have been described to Sort by Last Name in Excel.

Download Practice Sheet

Sort by Last Name in Excel (4 Methods)

If you work with named datasets, sorting is one of the common tasks you would have to do often. It’s quite easy to sort data alphabetically based on the full name, where Excel uses the first character of the name to sort. But what if you want to sort data by the last name in Excel?

In this article, we will show you how to sort data in Excel by Last Name.

1. Extract and Sort by Last Name Using Find & Replace

The first step to sorting a dataset by the last name is to get the last name in a separate column.

You can do this by replacing everything before the last name so that you only have the last name left. Suppose you have a dataset as shown below and you want to sort this data alphabetically using the last name.

Step 1: Select column A  => Copy column A to column B

Select a Dataset

Step 2: Select the column B => press Ctrl+H => Find and Replace menu appears.

Find and Replace menu appears

Step 3: Select the asterisk (*) symbol => click the Find Next option.

Select the asterisk (*) symbol

Last Name in column B appears

Step 4: Mark both column A & Column B => select Data => Click the Sort option => Sort menu appears

Click the Sort option

Step 5 :Sort by Last Name => Order A to Z => press Ok

Select A to Z

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

Full names appear alphabetically based on the last name.

2. Using Text to Columns Option

Here is a dataset containing Full names in column A. We will sort the last names using the Text to Column method in this section.

Dataset

 

Step 1: Select Data => then press the Text to Columns option

Press the Text to Columns option

Step 2: Convert Text to Column Wizard menu appears => Click Next

Convert Text to Column Wizard menu appears

Step 3: Select the Space option => Press Next

Select the Space option

Step 4: Press the markup option of the dialogue box.

Press the markup option of the dialogue box

Step 5: Select the empty cell B2 => text $B$2 appears in the dialogue box => Now Press the Mark down option

Text $B$2 appears in the dialogue box

Step 6:The previous menu appears and now the text $B$2 is in the dialogue box. Click the Finish button.

Text $B$2 is in the dialogue box

Then the Column B containing the last names appears.

The Column B containing the last names appears

Step 7: Now Press Data => Click on the Sort option => Then the Sort menu appears => Sort by Full Name => Click A to Z in the Order menu => Press Ok

Click on the Sort option

Sort by Full Name & click A to Z in the Order menu

 

Then the dataset including the full names appears alphabetically based on the last name

3. Extract and Alphabetize by Last Name Using Formula

The third method is sorting the dataset by LOast Name Using 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 in the cells of the column.

Step 1: Here’s the formula that will extract the last name from the full name.

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

Select the empty cell B2 and type the formula there and press Enter. Then the Last Name appears in the cell.

Type the formula

Step 2:  Apply the formula from B3 to B11 as well. Then the last names appear in column B.

Apply the formula from B3 to B11

4. Using Flash Fill

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 the expected result in the first couple of cells to get the overall result.

Step 1: Type the last names of the column A2 and A3 in the B2 and B3 columns respectively.

Type the last names in B2 and B3 columns respectively

Step 2: Select both the cells Hover the cursor over the bottom-right part of the selection. Then the cursor changes to a plus icon.

Hover the cursor over the bottom-right part of the selection

Step 3: Press the Plus Icon => Then Click the Flash Fill option

Click the Flash Fill option

Now the last names appear in column B.

The last names appear in column B.

Things to Remember

=> If there is a middle name or a salutation before the name (such as Mr or Ms.)

In such a case, 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.

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

Conclusion

So these are four different ways that you can use to sort data by the last name. The best method would be to use the Find and Replace technique, but if you want to make your results dynamic, the formula method is the way to go. We hope that we found this article useful. You can use any of these methods and share your thoughts with us. Feel free to ask any questions or give suggestions here in the comment section.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo