You've probably had to work with a list of first and last names written in an Excel spreadsheet before. If first and last names are together in the same cell, you will not be able to put them in alphabetical order according to the last names. First, you will need to separate the first name from the last name. Here is explained below how to do it.
Steps
Step 1. As in the example below, your spreadsheet contains first and last names together in the same cell
Step 2. In this case, hold the cursor over the "B" column header, until a down arrow appears; then click with the left mouse button to select the entire column, as shown below
Step 3. Then select the DATA tab and then the COLUMN TEXT button
Note that after the column you are changing you must have several blank columns. If necessary, highlight the column and insert 2-3 more columns. Otherwise, the conversion will write the data above the data contained in the subsequent columns.
Step 4. In the first window of the Convert Text to Columns Wizard, choose Delimited
The Fixed Width choice is fine if the segments to be separated all have the same width (for example when separating telephone numbers from area codes)
Step 5. In the second window of the Convert Text to Columns Wizard, choose the delimiter, which is what separates what you want to have into separate columns
In our case, it is simply a space, so we will select Space. You can also check "Treat consecutive delimiters as one".
-
If the names are separated by commas (for example Rossi, Paolo), then you will have to choose Comma as the delimiter, and so on.
Separate First Names and Last Names Into Separate Fields in a Microsoft Excel List Step 6 Step 6. In the third window of the Convert Text to Columns Wizard, choose "General" formatting and leave the rest unchanged
To continue, press the "Finish" button.
-
This area is changed only when dealing with numbers and dates.
Separate First Names and Last Names Into Separate Fields in a Microsoft Excel List Step 7 Step 7. Check what you have done
The spreadsheet should look like this.
Separate First Names and Last Names Into Separate Fields in a Microsoft Excel List Step 8 Step 8. Now you can change the header to Name and Surname and sort by surname if you want
Here's what the spreadsheet looks like after editing the headings and alphabetizing the surnames.
Advice
This can also be done with Excel 2003, not just the latest version
Warnings
- ALWAYS make a copy of your spreadsheet and work with that copy rather than the original.
- REMEMBER to insert extra columns to the right of the column you are changing; otherwise you will write in the columns that already contain other data!
-
