wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 15 people, some anonymous, worked to edit and improve it over time.
This article has been viewed 248,272 times.
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.
1You have your spreadsheet, with both first and last names combined as in this illustration.
2In 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.
3Next, 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.
4In 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).
5In 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.
6In 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.
7Review your work. The spreadsheet should look like this now.
8You 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.Advertisement
QuestionThis only applies to people with just a first and last name. What if I have more than 3-4 names?Community AnswerThen you have to enter it manually. The computer can't tell which one is a first name and which one is a last name, computers can only split whole words.
One does not need the newest version for this, it can also be done in Excel 2003.
- 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!
About this article
Reader Success Stories
"Great explanation. I looked at may sites but this is the best one."