In this blog, we would learn how to sort data be the last name. Many a time, you have an excel worksheet with names of persons and you wish to sort the names by the last name. That means the names should be in ascending or descending order based on the last name (surname).
Do you know how can this be achieved?
Let us take one sample dataset containing the names (FirstName_LastName) of persons.
When you try to sort this name list in either ascending or descending order, the excel would by default sort it by its first name (and not by the last name):
There is no standard excel tool or functionality that can be used to sort this list by its last name.
Now, let us begin with this blog and learn how can we sort the name list by its last name.
As mentioned earlier, there is no standard functionality in place that can help to sort a list by its last name. Therefore, we need to first bring the last name at the beginning of the cell, so that sorting can be done.
The entire method would involve two steps. The first one is to separate the last name from the full name. The next step is to sort the list of names by its last name.
Separate Last Name from Full Name
Step 1: Copy the names in the cells adjacent to it as shown in the screenshot below :
Now the next step is to remove the first name from the list of names mentioned in column B.
Step 2: Select the list of names in column B as shown in the screenshot below:
Step 3: Press Ctrl + H (Control key followed by H on your keyboard) to open ‘Find and Replace’ dialog box. Refer to the screenshot below:
Step 4: In the “Find What” section, enter the asterisk symbol followed by a space character on your keyboard (i.e. *<space>). And keep the “Replace With” section as it is.
Also Read: How to Sort a Table or Data in Excel
And press the “Replace All” button on the dialog box.
You would now notice that the first name got removed from the list of names in column B and you have the last name at the beginning of the cells.
The above-mentioned method is one of the methods to separate the last name from the full name in Excel. To learn more ways, read an excel blog on “How to Separate First and Last Name from Full Name”.
Once you have segregated the last name from the full name, let us now try to sort the list of names by the last name.
Sort List of Names by Last Name
Select the cell B1 as shown in the screenshot below:
Now, go to the “Home” tab. Click on the option “Sort & Filter”, under the group ‘Editing’.
From the drop-down list, click on the option “Sort A to Z”.
This brings us to all the steps.
Now, check the values in columns A and B in your worksheet.
You would notice both the lists get sorted based on the last name. Now you have your name list in column A sorted in ascending order.
This brings us to the end of this blog. Share your views and comments in the comment section below.