Sorting data effectively is a key skill for anyone working with large datasets in Microsoft Excel.
You may notice that sorting by first name is pretty straightforward, but doing the same by last name is a bit complicated and time-consuming. It becomes challenging for many people, especially when full names are in a single column.
In that case, you need to separate the last name from the full name and then use the sorting feature.
Let us discuss in detail the methods to split the first and last names, and then learn to sort by last name in Excel using different pre-built features and functions to keep your data organized and easy to navigate.
Download the Example Excel Sheet
You can download the following Excel file, which we used to demonstrate various sorting methods in this article.
Make sure you follow me through the article to strengthen your Excel skills. Practice is the key to success.
If you have your sheet ready, you can skip downloading the example sheet.
How to Sort by Last Name in Excel
As mentioned earlier, sorting by last name is a bit complicated. There are two steps to do so, as discussed in the following sections.
STEP #1 – Separate First and Last Names
Excel boasts a couple of functions and features that help you split or extract data from a cell.
Consider the following example where we have full names in the first column.
Our task is to separate first and last names.
Let’s begin,
Method #1 – Using the RIGHT Function
The RIGHT function in Excel is used to extract a specified number of characters from the right side of the selected cell. We need to combine it with the LEN and FIND functions to extract the last names of varying lengths.
Here are the steps:
- Open your desired spreadsheet
- Create a new column with the name “Last Name”. Refer to the following image,
- Select the first cell of the new column below the header, as shown below
- Type “=RIGHT”
- Select the first option from the popup or press the “Tab” on your keyboard
- Enter the text string or the cell reference of the text that you want to trim
(Here, we are considering the cell reference A2, which has the text string) - Press “,” to move to the next argument
- Type “LEN” and press “Tab” to select it
- Provide the cell reference “A2” to count the number of characters
- Complete the bracket for the LEN function using “)” on your keyboard
- Press “–”
(We do this here to subtract the position of the space from the total length of the text string) - Next, type “FIND” and press “Tab” to select it
- Put a space in double quotation marks using the “Spacebar” on your keyboard
- Press “,” to move to the next argument of the FIND function and enter the cell reference as “A2”
- Complete the FIND formula using “)” on your keyboard
- Now, complete the RIGHT formula using “)”
- Press “Enter”
Here’s how the final formula looks,
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
This formula would work if you have first and last names only.
But if you have a middle name also, you can use the formula
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
Note that in the above formula, we are using the cell reference “A2”; you need to replace it as per your dataset.
Method #2 – Using the Find and Replace Tool
To use this method, first, we will create a new column for extracting the last names, as done in the previous section.
This method includes removing everything before the last name.
Let’s begin,
- Select the full names in the new column
- Press “Ctrl + H” to open the Find and Replace tool
(You can see a dialog box appearing on the screen as shown below) - In the “Find what” field, enter “* “
(an asterisk followed by a space) - Leave the “Replace with” field empty, as shown below
- Click on “Replace All” to remove the first names, leaving behind the last names
Now, your column will contain just the last names, making it easy to sort the dataset alphabetically by last name.
Method #3 – Using the Text to Columns
Last but not least, it may be one of the most preferred methods to separate last names in Excel.
We need to add a new column, as discussed in the previous methods, to get the last name.
Here are the steps to use Text to Columns:
- Select the entire new column containing full names (excluding the header)
- Navigate to the “Data” tab
- Choose the “Text to Columns” option
(This will open a ‘Text to Column’ dialog box as shown below) - Now, tick the radio button before the option “Delimited”
- Press the “Next” button
- Now, select “Space” as delimiters and click on “Next”
(Uncheck anything else if selected) - Click on “Finish”
The entire process can be seen in the GIF below.
STEP #2 – Sort By Last Name
Once you have split the first and last names, you can sort by last names alphabetically very easily by using the below three methods.
Method #1- By Using the Filter
This is one of the easiest methods to sort data in alphabetical order.
- Select any cell of the table
- Go to the “Home” tab
- Click on “Sort & Filter” option to enable filters for the table
- Next, choose the “Filter” option from the popup
- You will see a dropdown across each column header
- Click the dropdown icon of the last name column
- Select “Sort A to Z” to sort the last names in ascending order or “Sort Z to A” to sort them in descending order
Method #2 – Using the SORT Function
The SORT function in Excel is used to sort the contents of an array in ascending or descending order.
The General syntax of the SORT function is as follows,
=SORT(array,[sort_index],[sort_order],[by_col])
Where,
- “array” – Represents the data range to be sorted
- “sort_index” – This argument needs to be replaced with the column number by which we wish to sort the table
- “sort_order” – Here, you need to enter the values as “1” or “-1” to sort the data in ascending or descending order
- “by_col” – It should always be TRUE when you want to sort by column and FALSE when you want to sort by row
Let’s see how to use this function to sort our last names in ascending order.
- Create a new table as shown below,
- Select the first cell of the table
- Type “=SORT”
- Select the first option from the popup or press the “Tab” on your keyboard
- Enter the data range that is to be sorted
(Here, the data range is A3:B14 and make sure you use the double quotation marks; otherwise, the formula would return an error) - Press “,”
- For the next argument, sort_index, which indicates the column number used as a reference for sorting
(The column number used here is 2) - Press “,” and enter the order in which you want to sort the data
(We are sorting it in ascending order, so enter 1) - Close the parenthesis “)” to end the formula
The complete formula would look like this,
=SORT(A3:B14,2,1)
The result will be a sorted list based on the last names, starting from the selected cell.
For more such tips and tricks to use Microsoft Excel like a pro, make sure you explore our blog. Click Here!