Separate First Names and Last Names Into Separate Fields in a Microsoft Excel List
If you work with spreadsheets and get a spreadsheet with first and last names combined, you know you cannot sort by last names. Sorting by the field with both first and last combined is of no use. In these cases you need to separate the first and last names prior to sorting.
Contents
Steps
- You have your spreadsheet, with both first and last names combined as in this illustration.
- In this example, you would hold your cursor over the "B" column heading until it makes a down arrow, and then left click the mouse to select the entire column as shown here.
- Next, you select the DATA tab, and then select the TEXT TO COLUMNS button. Note that you must have several empty columns after the column you are converting. If you need to, highlight the column and insert 2-3 new columns. Otherwise, the conversion will overwrite the data in successive columns.
- In the first window of the Text to Columns Wizard, you will choose DELIMITED.
- You only choose fixed width if the segments you want to separate are all exactly the same width (like separating area codes from phone numbers).
- In the second window of the Text to Columns Wizard, you choose the delimiter, or what separates the things you'd like in separate columns. In our case, it is simply a space, so we select space. You can also checkmark "Treat consecutive delimiters as one".
- If you had names separated by commas (like Brown, James), you would use the comma as the delimiter, etc.
- In the third window of the Text to Columns Wizard, choose "general" formatting and leave everything else as it is. Press the "Finish" button to continue.
- This area is only changed if you are dealing with numbers or dates.
- Review your work. The spreadsheet should look like this now.
- You can now change the headers to First Name and Last Name if you like, and sort by last name if you are so inclined. This is what the spreadsheet looks like with updated headers and sorted alpha by last name.
Tips
- One does not need the newest version for this, it can also be done in Excel 2003.
Warnings
- ALWAYS make a copy of your spreadsheet before you try this and work on the copy rather than the original!
- MAKE SURE to insert a few extra columns to the right of the column you are editing, because it will overwrite any columns you have populated if you don't!
