You’ve got yourself a spreadsheet full of people’s names and some idiot has failed to format the spreadsheet columns into first and surname, making it impossible to sort by surname. That idiot was me. Luckily, Excel is one step ahead of us morons, making it easy to split a column full of names into two separate columns with first and last names. Here’s how.
1. Insert a blank column
If you don’t have a blank column to the right of the one currently holding your names, you need to insert a blank one. This is where your surnames will be placed. To insert a column, right click on the letter at the top of the adjacent column (in our case B), click Insert and a blank column will appear.
2. Select Text to Columns
Now click on the letter at the top of your names column (in our case A) and the names should all be highlighted. In the menu at the top of the screen, click the Data tab and then click on Text to Columns.
3. Choose delimited by space
Now we need to tell Excel how to separate the two names. In our spreadsheet, the first and last names are separated by a space. In the pop-up window that appears choose Delimited and click Next.
Then on the next screen choose Space and click Next once more:
4. Finalise the separation
Another screen will pop up, giving you a preview of how your names will be separated. Click Finish.
5. Check your data
Your spreadsheet should now have two columns, one with first names and one with surnames. You will need to adjust the headings on the columns (if you have them) and maybe adjust the cell widths. It’s also worth combing through your data to make sure no errors have been introduced – double-barralled surnames with space in between might need fixing, for example.
NOW READ THIS: How do you convert Excel to CSV?
I need to split Last Name, First Name, Middle Name (Initial) that is in one column to 3 columns, First Name, Middle Name, Last Name. Why don’t you include how to do this? I find this same problem with other websites that explain exactly what you said to do but also have the first column with First Name Last Name. I think most companies wouldn’t have a column with First Name, Middle Name, Last Name. They would have Last Name, First Name Middle Initial or Name.