Updated: Nov 27, 2022
On many occasions in Excel, you have data in one column that you would like to separate into multiple ones.
A few examples are:
When you have a .csv file, where data is entirely grouped into a single column and separated by commas.
When you are dealing with data from a multiple-response question, and your file as each response separated by a slash.
If you have names in the format "Last Name, First Name" and would like last and first names in different columns.
In each of these situations - and many more - you can save yourself the tedious task of manually separating the data by using Excel's powerful Text-to-Columns feature. So how does it work? Let's see with that last example with the Last Name, First Name situation.
1) Select your data
2) In the Data tab, go to the Data group and click Text-to-Columns.
3) A dialog box opens as shown below. Choose the "Delimited" radio-button. We are choosing Delimited because there is a specific string of character, a space followed by a comma, that separates each field.
4) In Step 2 of the dialog box, tick both the "Space" and the "Comma" checkbox. Make sure every other checkbox is not ticked like below, and then hit "Next".
5) A preview will show up, like in the screenshot below. You can then customize the format of each of the column that will be created, but in this case the General format is fine.
6) Hit "Finish". Tadaaa! Your data is now split in 2 different columns.
Stay aware that beyond this example, there are many situations in Excel that can be solved with Text-to-Columns. So every time you need to split data, look if there is not a separator like a comma, a tab, a semicolon, a space, a slash, a "-" or any other thing that will uniquely delimit each field within your data.