One of the most annoying things to have to do with your data is to manually clean it up. Having to go line by line in Excel to reformat names personally makes me want to scream. Thankfully, Excel has a lot of lesser known features to take care of some of those more menial tasks - including separating names stored in one column into two separate columns.
If you have a list of people, but they are listed in the lastname, firstname format and you want to separate them into different columns, there is a built-in function in Excel 2013 called Text to Columns that makes this super easy. Here's how to do it:
1. Highlight the cells you want to separate:
2. Click on the Data tab of the ribbon and you will see several useful tools here. We are going to be exploring Text to Columns:
3. Clicking Text to Columns will start the wizard that walks you through your options. On the first screen of the wizard, we will select “Delimited” because our data is already separated by a comma, and click next:
Next, we will check the box next to comma and click next:
We can leave the data format as General and click Finish on the last screen of the wizard:
Now the names have been separated into different columns! You can use this same Text to Column wizard to separate other types of copy into multiple columns. Just walk through the wizard the same way, but choose the options that fit your data (For example, if your names are separated by a space, like "Joe Smith", choose "Space" instead of "Comma" on the second screen of the wizard).
Now you can separate text within cells like a pro! Want to turn your new, separated list of names into email addresses? There's an Excel formula for that - find out how on this blog post. Happy Excelling!
For more Office tips and news or to find out when we publish new blog posts, follow us on Twitter, Facebook and LinkedIn.
