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
Step 2: Select the column B => press Ctrl+H => Find and Replace menu appears.
Step 3: Select the asterisk (*) symbol => click the Find Next option.
Step 4: Mark both column A & Column B => select Data => Click the Sort option => Sort menu appears
Step 5 :Sort by Last Name => Order A to Z => press Ok
Then the dataset including the full names appears 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.
Step 1: Select Data => then press the Text to Columns option
Step 2: Convert Text to Column Wizard menu appears => Click Next
Step 3: Select the Space option => Press Next
Step 4: 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
Step 6:The previous menu appears and now the text $B$2 is in the dialogue box. Click the Finish button.
Then 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
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 Last 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.
Select the empty cell B2 and type the formula there and press Enter. Then the Last Name appears in the cell.
Step 2: Apply the formula from B3 to B11 as well. Then the last names appear in column B.
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.
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.
Step 3: Press the Plus Icon => Then Click the Flash Fill option
Now 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:
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.
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.