Splitting comma delimited numbers in a single cell in an EXCEL column into separate columns using these commas
There are two ways to split numbers in a single cell separated by commas into separate columns using the commas. If you have only a few columns to reformat then:
- Highlight all the cells, or highlight the column.
- Then click on "Data", "Text To Columns...", click on the "Delimited" radio button, then click on the "Next" button, in "Delimiters" put a checkmark in "Comma", then click on the "Finish" button.
Alternatively if you have a lot of columns then it may be better to do the below.
Save the EXCEL worksheet as a text file (via the office button, save as>other formats),
- Now open this text file and replace the “ (double speech marks at the start and end of each column cluster) with spaces and save.
- Read this text file into SPSS and it should produce separate columns for each number as it recognizes comma delimited data. (This may produce some extra columns which can of course be deleted). You can then save this file in SPSS as an EXCEL file (using ‘save as’ in the file menu (make sure you are in the spreadsheet window when you do this)). This file will then have separate columns for each number.