Excel Tip - Splitting single cell data into separate cells

Last time we looked at combining data from separate columns into one single cell, this time what if you have your data arranged in a single cell and you need to split it into individual columns?  Excel makes it easy, you can use the Text to Columns feature...

 

  • Select the cells that need to be split:

 

  • Choose the Data menu, then Text to Columns in the Data Tools Group:

 

  • The Convert Text to Columns Wizard will start, select Delimited, then Next:

 

  • In step 2, select Space as the Delimiter, then Next:

 

  • Select the Column data format to correspond with the type of data to be placed in each column – General or Text for text data; Date for dates.

 

  • Click in the Destination: box, click in the spreadsheet to select a cell for the data to be copied to. In the example below we have click in the spreadsheet on cell C1:

 

  • Then click on Finish to end and the data will be separated into three columns:

 

  • The convert Text to Columns feature in Excel can save you so much time when data isn’t organised in the way that you need it to be, it will save you from retyping!
Pin it