If you work with names datasets, sorting it 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? While it’s not as straightforward, it can still be done (a lot also depends on the way names data is structured). No matter what method you use, you will have to, somehow, extract the last name from the full name and put it in a separate column. You can then use this column to sort your data by the last name alphabetically. In this Excel tutorial, I will show you how to sort a column with names based on the last name. So let’s get started!
Extract and Sort by Last Name Using Find and Replace
The first step to sorting by the last name is to get the last name in a separate column. You can do that by replacing everything before the last name with a blank 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.
Below are the steps to sort by the last name: The above steps would keep the last name and remove everything before it. This works well even when you have middle names or prefixes (such as Mr. or Ms).
Once you have the last names in the adjacent column, you can easily sort the dataset (including the full names) alphabetically based on the last name. Below are the steps to sort by the last name: The above steps would sort the entire selected dataset based on the last name.
Once done, you can delete the column that has the last name.
Extract and Alphabetize by Last Name Using Formula
While the method that’s shown above (using Find and Replace) is what I prefer to get all the last names and sort based on it, one limitation of it is that the resulting data in static. This means that if I add more names to my list, I will have to do the same process again to get the last names. If this is something you don’t want, you can use the formula method to sort data by last names. Suppose you have the dataset as shown below. Below is the formula that will extract the last name from the full name:
The above formula relies on the pattern with a full name (that contains only the first and last name in this example). The pattern is that there would be a space character between the first and last name. The FIND function is used to get the position of the space character. This value is then subtracted from the total length of the name to get the total number of characters in the last name. This value is then used in the RIGHT function to get the last name. Once you have the last name column, you can sort this data (this is covered in the first method in detail). The above formula would work when you only have first and last names. But what if you have a middle name as well. Or may there is 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. I recommend you use the second formula in all the cases, and it’s more fool-proof and can handle all cases (as long as the last name is at the end of the name). Note: These two formulas rely on the condition that there is only one space character between every name element. In case there are double spaces, or leading/trailing spaces, this formula will give incorrect results. In such a case, it’s best to use the TRIM function to first get rid of any leading, trailing and double spaces, and then use the above formula. While this may seem like a complicated method, the benefit of using a formula is that it makes the results dynamic. If you add more names to your list, all you have to do is copy the formula and it will give you the last name.
Using Text to Columns
Text to Columns is again a simple and easy way to split cells in Excel. You can specify the delimiter (such as comma or space) and use it to split the content of the cell. Once you have the split elements in separate columns, you can use the column that has the last name to alphabetize the data. Suppose you have a dataset as shown below: Below are the steps to use Text to Column to sort by the last name: Once you have the result, you can sort by the last name.
You can also Text to Columns to separate first and last names when you have a comma as the separator.
Using Flash Fill
Another quick and fast way to get the last names is using the Flash Fill feature. Flash Fill was introduced in Excel 2013 and it helps manipulate the data by identifying patterns. For this to work, you need to show Flash Fill the result you expect a couple of times. Once it identifies the pattern, it will quickly do the rest of the work for you. Suppose you have the below names dataset.
Below are the steps to use Flash Fill to get the last name and then sort using it: This will give you the result which will likely be the last names in all the cells. I say likely, as Flash Fill may not work in some cases. Since it depends on identifying a pattern, it may not be able to do that always. Or sometimes, the pattern it deciphers may not the right one. In such cases, you should enter an expected result in one or two more cells and then do steps 4-7. Once you have all the last names in a column, you can sort the data based on these last names. 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. Hope you found this tutorial useful. You may also like the following Excel tutorials:
How to Sort By Color in Excel How to Sort Worksheets in Excel How to Sort Data in Excel using VBA Automatically Sort Data in Alphabetical Order using Formula How to do a Multiple Level Data Sorting in Excel Flip Data in Excel | Reverse Order of Data in Column/Row How to Combine First and Last Name in Excel How to Shuffle a List of Items/Names in Excel? 2 Easy Formulas! Extract Last Name in Excel (5 Easy Ways)
