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.
- Sort full name column by last name with Text to Column command
- Sort full name column by last name with Find and Replace command
- Sort full name column by last name with Kutools for Excel
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
As we know, the Microsoft Excel's Text to Column command can help us to split the full name column to the first name column, middle name column, and last name column and so on. To sort full name by last name will be easy if we can get the last name firstly.
Step 1: Select the full name column you will split.
Step 2: Click the Text to Column button under the Data tab.
Step 3: Follow the wizard to finish the splitting. Please choose the Space delimiters in the Step 2 of 3 Wizard.
Then it split the full name column into several columns as the following screen shot shows:
Step 4: Following our example, enter the formula of =TRIM(IF(OR(LEN(C1)=0),B1,C1&" "&D1)) in a new column, says Column F.
Step 5: Copy the Column F, and Paste Special as plain text in a new Column, says Column G to clear the formulas. You can also do it by Kutools for Excel's Fill Cells with Displayed Values tool.
Step 6: Keep selecting the Column F, and click the Sort button under Data tab. In the popping up Sort Warning dialog box, check the Expand the selection option.
Step 7: Click Sort button.
Then it sorts all split columns by the last names column now.
It seems a little hard to sort full name column by last name with the Text to column command. Actually, the Microsoft Excel's Find and Replace command can help you do it quickly.
Step 1: Copy the full name column, and paste it in a new column, says Column B.
Step 2: Click the Home >> Find & Select >> Replace to open the Find and Replace dialog box. You can also open it with pressing the Ctrl + F keys together.
Step 3: In the Find What: box, type a blank space; and in the Replace with: box, type the @, and click Replace All.
Step 4: Still in the Find and Replace dialog box, enter the ?*@ in the Find what: box, and enter nothing in the Replace with: box, and click Replace All button.
Step 5: Keep selecting the Column F, and click the Sort button under Data tab. In the popping up Sort Warning dialog box, check the Expand the selection option.
Step 6: Click Sort button.
Now the original full name column is sorted by the last name immediately.
1. Select the names 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, and choose Last name from Sort On drop-down list and the order of sorting you need. See screenshot:
3. Click Ok, the full name list has been sorted by last name, then click Cancel to close the dialog. See the result as shown as below.
If the sorted list has header, please check My data has headers in the Advanced Sort dialog.
With Advanced Sort feature, you also can sort data by text length, frequency and so on. Click here for more information about Advanced Sort.