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

Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Learn more Free download

Sort full name column by last name with Text to Column command

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.

Sort full name column by last name with Find and Replace command

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.