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.

arrow-big
Kutools for Excel: add 120 new features in Excel. Save one hour every day.
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.

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

arrow blue right bubbleSort 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.


arrow blue right bubbleSort 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.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+2#utihomes2014-03-08 19:11
I would put your second example at the top. It is much better than the "text-to-column" option.
Reply | Reply with quote | Quote
-1#Donnie2014-03-26 18:15
In light of this evidence it is clear that the extended knowledge adults have of their first language can
and often does, impede the process of learning a second language.
As a matter of fact, if you bring up controversial topics, there is
good chance that it will have the opposite than your desired effect.
Have fun with your students and they'll enjoy learning from you.


Also visit my blog post ... english courses
Reply | Reply with quote | Quote

Add comment


Security code
Refresh