Excell 2007: Split contents in cells of excel worksheets

In Excel (including Excel 2007), contents of cells can be split and displayed across other cells, based on a delimiter.The following is a tutorial on splitting the contents of cells that are not merged, in excel worksheets, across multiple columns.

How to split contents in cells of excel worksheets?

  • Select the cell, the range of cells, or the entire column that contains the text values, that you want to divide across other cells, based on a delimiter. A range can be any number of rows tall, but no more than one column wide.
  • On the Data menu, click Text to Columns
Split contents in Excel cells
  • In step 1 of “convert to Text Columns wizard“, choose “Delimited” to specify the delimiter that should be used, to split the contents of the cells. Click “Next
Excel convert to text columns wizard
  • In step 2 of “convert to Text Columns wizard”, choose the actual delimiter. The values in the cells can be delimited by comma or tab or semicolon or space or any other.In this example, the delimiter is comma. You can also specify text qualifier and the way to treat consecutive delimiters. Click “Next”
How to split contents in cells of excel worksheets
  • In step 3 of “convert to Text Columns wizard”, you can choose the Column data format.In this example we have chosen “General“. “General” converts numeric values to numbers, date values to dates and all remaining values to text.
  • Choose the destination cell (starting cell) where the split values should be displayed.However, note that unless there are one or more blank columns to the right of the selected column, the data to the right of the selected column will be overwritten.Hence choose the start destination cell, for the resultant output, carefully. In this example, I had chosen B2 as the start cell, for the resultant output.
choose the column data format of cells whose values need to be split
  • Finally, click “Finish“.The resultant output will look as shown in the figure below
Click finish to split contents

You can also join or merge the excel column contents. Did you like the tutorial on how to split contents of cells in Excel 2007 worksheet into multiple cells, based on delimiter?

5 comments on “Excell 2007: Split contents in cells of excel worksheets

  1. Nice tutorial dude!
    The screenshots really help out with understanding…

    Fahad’s last blog post..Leaving blogging for 2 and a half months……..

  2. […] earlier saw how to split column contents in excel worksheets? Now if you have text in two or more columns of your excel worksheet, and you wish to join […]

  3. How do I split numeric data instead of alphabets as shown in the example?

  4. You can also split numeric data using the same technique.

  5. Hi,

    I tried this in Excel 2007. An example of the single cell value to split is ‘Batch 101-19061-PACK01-19061-CRF.pdf’. The options chosen when using the ‘text to columns’ function are Delimited, Tab and General (& chose a destination before FINISH).

    However, the cell value wasn’t split. The output remained the same as the input value. What is the problem here? Any advice would be appreciated.

  6. You have selected tab as the delimiter. You should instead specify hyphen (“-“) as the delimiter.

Leave a Reply

Your email address will not be published. Required fields are marked *