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 unmerged cells, in excel worksheets, across multiple columns.

How to split contents in unmerged 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

• 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”

• 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”

• 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.

• Finally, click “Finish“.The resultant output will look as shown in the figure below

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

Filed under: MS Excel Tips office 2007