Sorting names alphabetically in Excel

During each university entrance exam, the statistics department compiles and arranges the list of candidate names alphabetically to allocate examination rooms. Is this task difficult and time-consuming for you? The problem becomes much easier when the list is already sorted alphabetically. Let me introduce you to how to sort names alphabetically.

Typically, both the first and last names are entered in the same column. So, to sort by name, you need to separate the first name, middle name, and last name.

So, to sort, you need to perform the following tasks:

1. Separate names from the full name column

Step 1: Create 2 additional columns next to the Full Name column (name the columns First Name and Last Name) and copy all data from the Last Name column to the First Name column.

Step 2: Select the entire Last Name column and press the Ctrl + H key combination.

- The Find and Replace dialog appears: In the Find What field, enter the character * and a space; leave the Replace field blank. Then click Replace All.

- The notification dialog box appears, click OK and Close on the Find and Replace dialog box. The result is the trimmed name (as shown in the illustration).

Step 3: Use the left() and Len() functions to retrieve the Last Name and Middle Name values. In the Last Name column, input the following command: LEFT(C3,LEN(C3)-LEN(E3)-1).

Result of retrieving the Last Name value (as illustrated):

Step 4: Copy the entire Last Name and Middle Name columns - then select Paste Value back into the current position.

Note: Select the Paste Value option to avoid errors when deleting the Full Name column.

Step 5: Delete the Full Name column. Insert 2 additional columns named First Name and Middle Name. Copy all data from the Last Name column to the Middle Name column.

Step 6: Select the entire Middle Name column, then press the Ctrl + H key combination. The dialog box appears. In the Find What field, enter the character * and a space; leave the Replace field blank. Then click Replace All.

The result is the trimmed middle name.

Step 7: Enter the following command into cell C1 to retrieve the Last Name value (as shown in the illustration).

Result:

Step 8: Copy the Last Name and Middle Name columns and paste them back into the current position (using Paste Value) to avoid errors when deleting the Last Name column.

Step 9: Delete the Last Name column. The result is as follows:

Step 10: Click on Data ->Sort (as shown in the illustration).

Step 11: When the dialog box appears, select Add Level to add a sorting field.

Note: Sort in the order of First Name => Middle Name => Last Name.

The result after sorting is as follows:

Step 12: Combine the 3 columns Last Name, Middle Name, and First Name into one column.

There are numerous ways to do it, but let me introduce the simplest: Insert a column labeled Surname and input the command as depicted:

Step 13: Duplicate the Surname column and choose Paste Value at the original position to ensure no errors when removing the 3 columns Last Name, Middle Name, First Name.

Step 14: Delete the 3 columns Last Name, Middle Name, and First Name, remembering to adjust the sequence numbers accordingly. Your data is now sorted by name.

Wishing you all success!