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.
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 .
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 .
- A dialog box appears, click OK and Close the Find and Repalce dialog box. The result is cut name (as shown).
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) .
The result takes the value they (as shown):
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.
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 .
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 .
The result has been cut off the middle name.
Step 7: Enter the following statement in cell C1 to get the value they (as pictured).
Result:
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.
Step 9: Delete the Last column . The result has the following data table:
Step 10: Click Data - > Sort (as shown).
Step 11: A dialog box appears, click Add Level to add a sort field.
Note: Sort by Name => Middle name => Last name .
Results after sorting:
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:
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.
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:
Good luck!