If you regularly import CSV’s into Excel you may find that sometimes the Excel CSV opens in one column instead of in separate columns as you would expect. This happens often when you have changed computers or received a file from a different source.
Convert one column CSV into separate columns within Excel
The quick way to fix this is to effectively re run the CSV logic but on the existing single column version. This can be done using the Text to Column tool which asks exactly the same questions as if you used the ‘Import from Text’ option when opening the file.
So as shown below, we have opened a CSV file in Excel, but instead of putting the data into separate columns, it is all in Column A and we can see the commas. Ideally we wanted the commas to show Excel where to split the columns.
Once you have it in Excel, you can do the following:
- Highlight the cells that are in one column
- Click on the Data Ribbon
- Click on the Text to Column button
- Choose the Delimited option if not already chosen
- Click Next
On the next screen as shown below
- Tick the box marked ‘Comma’. In this case this is the correct one, but depending what you see when you open the file, you may choose ‘Semi Colon’
- Excel will put lines where it is going to separate the columns. Check that these make sense to you.
- You can now click the ‘Next’ button
The next screen is useful for controlling how you want to see the data. You can see its uses in the ‘Related’ posts at the bottom. You can now click the ‘Finish’ button.
Excel will now convert the csv that opened in one column to a version that splits it into its own columns as shown below. Note that the commas from above have disappeared as Excel has assumed that each comma implied a new column.