How to Sort by Last Name in Excel (Fast & Easy)

When sorting names, Excel uses the first character of the full names, and sort data in alphabetical order. It’s quite easy.

But what if you need to sort data by their last names? 🤔

It’s not a straightforward process but it can be done in Excel. Not to worry, we’ll show you different ways how to sort by last name in Excel even when you’re given a list of full names to sort. 👍

To start, download this sample workbook for this Excel tutorial.

Table of Contents

Extract and Sort by Last Name Using a Formula

There are many ways to write names. The most common way to write is in the format of First Name and Last Name. ✍

When names are written in this format, it’s difficult to sort data by last names as Excel uses the first character of the full names and sort data alphabetically.

To be able to sort by the last name, we need to extract the last name first, and then sort.

Open your sample workbook and let’s extract the last name from the full names in Column A.

Step 1) In column B, click cell B2, and type this formula:

Step 2) Press Enter.

Step 3) Drag the fill handle down to fill the rest of your cells.

You’ve now extracted the last name from the full names in column A using the formula above. Before proceeding, let me explain how the formula worked. 💡

As you can see, full names follow the format First Name first, followed by a space, and then the Last name. The space separates the First name and last names. We can use this knowledge to extract the last name.

There are 3 Excel functions used for this formula: the LEN, FIND, and RIGHT functions.

The LEN function returns the length of a given text string as the number of characters including the spaces. So, the name in cell A2 has 12 characters.

Next, the FIND function returns the number of the starting position of one text string within another text string. We used this to find the position of the space between the first name and last name in a text string.

In our example, it’s position 7.

The difference between the LEN and FIND functions is 12 – 7 = 5.

We use this value for the RIGHT function to return the specified number of characters from the end of the text string. And we’ve successfully extracted the last names. 👍

Now it’s time to sort.

Step 4) Select the cells including the headers.

Step 5) Go to the Data Tab.

Step 6) In the Sort & Filter group, click the Sort icon. The Sort dialog box will appear.

Step 7) Check the My Data has headers checkbox.

Step 8) Under the Sort by Column field, select Last Name.

Step 9) Under the Sort on field, select Cell Values.

Step 10) Under the Sort Order field, select A to Z.

Step 11) Click OK.

This is now the result. You’ve successfully sorted data by last name. 😀

Once done, you can delete the column that has the last name.

Extract and Sort by Last Name Using Find and Replace

Another way to sort by last name in Excel is using Find and Replace. Doing this method is quite similar to the method used above: extract the last names from the full name first, then Sort.

Similar but not all the same.

Let’s try it. 👇

Step 1) Copy the data in column A and paste it in Column B.

Step 2) Go to the Home Tab.

Step 3) In the Editing group, click Find and Select.

Step 4) Select Replace from the drop-down.

The Find and Replace dialog box will appear. You can also Press Ctrl + H to quickly open the Find and Replace dialog box.

Step 5) In the Find What field, type an asterisk (*) and space ( ) characters.

Step 6) In the Replace With field, leave it blank.

Step 7) Click Replace All.

Step 8) A Microsoft Excel dialog box will appear informing you of the 5 replacements done. Click OK and then close the Find and Replace dialog box.

That’s it. 🙌

The steps above keep the last name and remove everything else before it. Even when your list of names contains salutation prefixes such as Mr/Ms/Mrs, or have middle names, this method works well.

Now that you have the Last Names, you can sort the data. Follow the same steps above to sort the data by last name and delete the Last name column once done.

Split Names Using Text to Columns and then Sort by Last Name

Here’s another way to sort by last name: using Text to Columns in Excel. In this method, we’ll split the names and place them in separate columns using a delimiter first. Once we have the last names, we can sort data.

Let’s give this method a try.

Step 1) Select the cells with full names.

Step 2) Go to the Data Tab.

Step 3) In the Data Tools group, click the Text to Columns button. This will open the Convert Text to Columns Wizard. 🧙‍♂️

Step 4) In Step 1 of the Convert Text to Columns Wizard, under the Original Data Type, click Delimited, and click Next.

Step 5) In Step 2, under the Delimiters field, you’ll find different delimiters such as tab, semi colon, and comma. In our example, the delimiter is the space between the first name and last name. Check the Space checkbox, and click Next.

Step 6) In Step 3, under the Column data format, click the Do not import column.

Step 7) Still in Step 3, under the Destination field, specify the destination cell. Click cell B16 in your worksheet.

Step 8) Finally, click Finish.

This is how your worksheet should look like. 👀

With the last names now ready, you can proceed to sorting.

Split Names Using Flash Fill and then Sort by Last Name

The easiest way to separate the Last name from your list of full names is using Flash Fill. ⚡

Flash Fill automatically fills your data when it senses a pattern.

Step 1) In a new cell, type the last name of the first person, and press Enter.

Step 2) In the next cell below, start typing the last name of the next person. Excel will then sense the pattern you provide, and show you a preview of the rest of the column filled in.

Step 3) To accept the preview, press Enter.

You can now sort data by last name.

What method will you use to sort by last name in Excel? 😊

Whatever the method, this is how the sorted data should look like.

That’s It—Now What?

Amazing! 👏

I believe you’ve mastered how to sort by last name in Excel. You can use any of the methods above to first extract the last names, and then sort data.

Now that the names are sorted, you may be wondering what else is there to sort in Excel?

Level up your sorting game with related Excel articles below: 🚀

• How to Sort Columns in Excel (Without Mixing Data)
• How to Sort Multiple Columns in Excel (Many Examples)
• How to Sort Numbers in Excel (Lowest to Highest, etc.)
• How to Sort by Date in Excel in 2 Minutes or Less
• How to Sort a Pivot Table in Excel: Step-by-Step
• Excel SORT Function: Sort Excel Data Using a Formula
• How to Unsort in Excel: Revert Back to Original Sorting

Also, check out how to easily switch first and last names!