Excel 2013/2016 – How to split comma separated values into columns

The following steps show how to split comma separated values from one column into many.

In this example we’ll focus on comma separated values, but the same process can be applied to any other character separated values – e.g. tab, semi-column, space

For example, if you have an Excel document that looks something like …

1	Cat	Good,Bad,Mad,Happy
2	Dog	Bad,Good,Happy,Silly,Friendly
3	Bird	Good

and you need to split the third column of comma separated values into their own columns.

1. Select the column that contains the values you want to split
2. 
3. Click on ‘DATA’ in the ribbon menu
4. Click on the ‘Text to Columns’ button
5. 
6. The ‘Convert Text to Columns Wizard’ will open
7. Leave ‘Delimited’ selected and click ‘Next’
8. 
9. Under ‘Delimiters’, untick ‘Tab’ and tick ‘Comma’ (note – here you can also choose other delimiters or enter your own, like the “pipe” character – | )
10. Click ‘Next’
11. 
12. Here you can specify what type of data is in each column – by default ‘General’ is selected. If your data contains numbers I recommend changing to ‘Text’ by selecting all the columns in the preview and clicking on ‘Text’
13. Click ‘Finish’
14. 
15. The data has now been separated into their own columns
16.