How to sort full name by last name in Excel?

This method will introduce the Find and Replace feature to extract the last names from all full names, and then you can sort full names by the last name easily. Please do as follows:

1. Copy the full name column, and paste it beside the full name column, says Column B.

2. Keep selecting the pasted column, and press the Ctrl + H keys simultaneously to open the Find and Replace dialog box.
Note: You can also open this dialog box by clicking Home > Find & Select > Replace.

3. In the opening Find and Replace dialog box, please type a space into the Find What: box, type the @ into the Replace with: box, and then click the Replace All button. See screenshot:

And then a dialog box will pop up and show you how many replacements have been made, just click the OK button to close it.

4. Still select the new column, and in the Find and Replace dialog box, enter the *@ in the Find what: box, enter nothing in the Replace with: box, and then click Replace All button.

And then a dialog box will pop up and show you how many replacements have been made, just click the OK button to close it.

5. Keep selecting the new column, and click Data > Sort A to Z or Sort Z to A, and then in the Sort warning dialog box please check the Expand the selection option, and click the Sort button. See screenshot:

So far the full name column has been sorted by the extracted last names. If necessary, you can delete the new extracted last name column.

Using Text to Column function or Find and Replace function to sort name by last name are no easier than applying Advanced Sort feature of Kutools for Excel.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the full name column you want to sort by last name, and click Kutools Plus > Advanced Sort to enable this feature.

2. In the pop-up Advanced Sort dialog, select the column you want to sort, choose Last name from Sort On drop down list, specified the sort order from the Order drop down list, and click the Ok button. See screenshot:

So far the full name column has been sorted by last name at once.