How to sort full name by last name in Excel?

This method demonstrates how to use the Find and Replace function in Excel to swiftly extract last names from strings of full names. Once you have the last names isolated, you can then easily sort the list of full names by their last names. This technique works best with simple name formats—especially when each full name consists of just two parts (such as "John Smith"). For names with more than two components, you may need to check the extraction results carefully for accuracy.

1. Select the column containing your full names. Right-click and copy the entire column, then paste it into an adjacent blank column (for example, in Column B) to create a helper column for processing without altering your original data.

2. With the helper column still selected, press Ctrl + H together to open the Find and Replace dialog box.
Tip: Alternatively, you can access this dialog box by clicking Home > Find & Select > Replace.

3. In the dialog, enter a space character in the Find What field and an @ symbol in the Replace with field. Click Replace All.

A message will appear showing how many replacements were made. Click OK to proceed.

4. Keeping the same column selected, enter *@ into the Find what field, leave Replace with blank, and click Replace All. This step removes the first name from each cell, leaving only the last name.

Click OK when prompted.

5. To finish, highlight the helper column you just processed and click Data > Sort A to Z (or Sort Z to A, depending on your needs). In the "Sort Warning" popup, select Expand the selection and click Sort to keep your full name entries intact during the sort.

After sorting, your list will be organized according to last names. You may delete the helper column if you wish to keep only the sorted full names. Please note that extracting and sorting last names with this method may require extra attention if your names include middle names, initials, or compound surnames.

While using built-in Excel features such as Text to Column or Find and Replace is common, a more streamlined approach involves utilizing the Advanced Sort feature in Kutools for Excel. This dedicated sorting tool enables you to sort your full names by their last names directly and conveniently, even in lists where names have varied lengths or more than two parts. It’s especially suitable for large datasets where you want accurate results with minimal effort.

Kutools for Excel contains over 300 practical tools to enhance Excel productivity. You can experience all features for free for 30 days with no credit card required.Get It Now

1. Select your full name column and then go to Kutools Plus > Advanced Sort to activate the sorting dialog.

2. In the Advanced Sort dialog, set up your desired sorting conditions: select the column to sort, select Last name under Sort On, choose your preferred sort order (A to Z or Z to A), and finally click OK.

Your list will now appear sorted by last name in just a few clicks. This method helps reduce accidental mis-sorting and saves you time.

For many users, extracting last names via Excel formulas offers a flexible, repeatable way to sort records when dealing with standard full name structures. This technique is useful if you wish to automate name extraction for large lists, and is especially effective when your names always follow the "First Last" format. However, for names with middle names or multi-word surnames, formulas may require adjustment or more complex logic.

1. In a blank column next to your full names (e.g., in cell B2 if your names start from A2), enter the following formula:

=TRIM(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

This formula extracts the rightmost word from the full name. It works by replacing the last space with an "@", then finding everything to the right of that marker. If your data is in a different column, adjust the A2 reference accordingly.

2. Press Enter. Then, drag the formula down to fill all corresponding rows in your helper column. This step produces a column of last names for each full name.

3. With your last names extracted, select both the full name and the helper column, then click Data > Sort. In the Sort dialog, set the helper column (containing last names) as the criterion for sorting. Apply ascending or descending order as needed.

Tip: You can hide or delete the helper column after sorting if it is no longer needed. If your data includes middle names, you may need to further adjust the formula (for example, by splitting using MID and SEARCH functions).