Log in
x
or
x
x
Register
x

or

How to sort full name by last name in Excel?

It is quite easy to sort a full name column with the first name. However, sometimes you may want to sort the full name column by the last name. How could you sort the full name by last name in Excel? This article brings you some tricky ways to help you sort full names column by last name in Excel easily.

Easily sort by last name/word in cells in Excel

Kutools for Excel's Advanced Sort utility supports sorting data by many flexible scenario/methods, such as sort by text length, sort by frequency, sort by last name, etc. Full Feature Free Trial 30-day!

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.


arrow blue right bubbleSort full name column by last name with Find and Replace feature

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.


arrow blue right bubble Sort by last name with extracting the last name firstly

If we can extracting the last name from each full name, and then it’s easy to sort the list of full names by the last names. This method will introduce Kutools for Excel’s Split Names utility to extract the last names firstly, and then sort by last name.

1. Select the full name column without column header, and click Kutools > Text > Split Names. And then in the opening Split Names dialog box, please check the Last name option only, and click the Ok button. See screenshot:

2. In the second Split Names dialog box, please specified the first cell of destination column beside original full name column, and click the OK button.
And then all last names are extracted from full names and placed beside original full name column. See screenshot:

3. Select the new last name 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:

And now the full name column is sorted by the last names. If necessary, please delete the new created last name column as you need.


arrow blue right bubbleSort by last name with reversing the first and last names

Actually, we can reverse the full name cell and place the last name at the beginning of cell by the Reverse Text Order utility of Kutools for Excel, therefore it will be quite easy to sort by last names.

1. Select the full name column without the column header, and click Kutools > Text > Reverse Text Order. See left screenshot:

2. In the opening Reverse Text dialog box, please check the Space option, and click the Ok button. See screenshot:

3. Select the full name column, and then click Data > Sort A to Z or Sort Z to A. So far the full name column has been sorted by the last name already. But we need to restore the normal full name order.

4. Repeat Step 1 and Step 2 to restore normal full name order.
Please Select the full name column without the column header, and click Kutools > Text > Reverse Text Order. And then check the Space option and click the Ok button in the Reverse Text dialog box. See screenshot:


arrow blue right bubbleSort full name column by last name with Kutools for Excel

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.

1. Select the full name column you want to sort by last name, and click Enterprise > Advanced Sort. See screenshot:

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.


arrow blue right bubbleDemo: sort full names by last name in Excel

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Relative Articles


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Farid · 11 months ago
    I used the find & replace technique, it was a brilliant idea. Thanks very much
  • To post as a guest, your comment is unpublished.
    utihomes · 5 years ago
    I would put your second example at the top. It is much better than the "text-to-column" option.