Sort names alphabetically in Excel

At each university exam, the statistics department will list and arrange candidates in alphabetical order to arrange the exam room. This job is very difficult and takes a lot of time, isn't it? The problem becomes easy once the list has been sorted alphabetically. I will introduce you how to arrange names by abc.

Usually first and last names enter in the same column. So to sort by first name must cut first name, middle name and last name.

Sort names alphabetically in Excel Picture 1

So to organize you need to do the following:

1. Separate first name from first and last column

Step 1: Create two columns next to Full name (name the column They and column name ) to copy the entire data in the column they to the column name .

Sort names alphabetically in Excel Picture 2

Step 2: Choose your entire column name , press Ctrl + H .

- The Find and Replace dialog box appears: In the Find What section, you enter the * characters and spaces; Replace item is empty. Then click Repalce All .

Sort names alphabetically in Excel Picture 3

- A dialog box appears, click OK and Close the Find and Repalce dialog box. The result is cut name (as shown).

Sort names alphabetically in Excel Picture 4

Step 3: Use the left () and Len () functions to get the First and Last name value . In the column They enter the following statement: LEFT (C3, LEN (C3) -LEN (E3) -1) .

Sort names alphabetically in Excel Picture 5

The result takes the value they (as shown):

Sort names alphabetically in Excel Picture 6

Step 4: Copy the entire column Name and column Name -> select Paste Value back to the current position.

Note: Select the Paste Value valueso that when deleting the First and Last Name column,there are no errors.

Sort names alphabetically in Excel Picture 7

Step 5: Delete the First name and last name column . Insert 2 more columns named First Name and Middle Name column . Copy the entire data from the Last column to the Middle name column .

Sort names alphabetically in Excel Picture 8

Step 6: Highlight the entire column Middle name press Ctrl + H , the dialog box will appear Find What Item you enter the * characters and spaces; Replace item is empty. Then click Repalce All .

Sort names alphabetically in Excel Picture 9

The result has been cut off the middle name.

Sort names alphabetically in Excel Picture 10

Step 7: Enter the following statement in cell C1 to get the value they (as pictured).

Sort names alphabetically in Excel Picture 11

Result:

Sort names alphabetically in Excel Picture 12

Step 8: Copy the First name column and the middle name column paste back into the current position (using Paste Value ) so that when deleting the first name column and middle name is not error.

Sort names alphabetically in Excel Picture 13

Step 9: Delete the Last column . The result has the following data table:

Sort names alphabetically in Excel Picture 14

Step 10: Click Data - > Sort (as shown).

Sort names alphabetically in Excel Picture 15

Step 11: A dialog box appears, click Add Level to add a sort field.

Note: Sort by Name => Middle name => Last name .

Sort names alphabetically in Excel Picture 16

Results after sorting:

Sort names alphabetically in Excel Picture 17

Step 12: Combine 3 columns First name , Middle name and First name into one column.

There are many ways, I introduce the simplest way: Insert a column to put Full name and type the command as shown:

Sort names alphabetically in Excel Picture 18

Step 13: Copy the First name column and select Paste Value in the same position so that when deleting 3 columns First name , Middle name , First name without errors.

Sort names alphabetically in Excel Picture 19

Step 14: Delete 3 columns Last name , Middle name and First name , pay attention to correct the ordinal number again. So your data has been sorted by name:

Sort names alphabetically in Excel Picture 20

Good luck!