1. Home
  2. MS Office

Excel 2010: Split Column Data Into Two [Parse Cells]

Excel 2010 includes a feature to parse cells. It could be helpful when we need to split data in to columns without entering the data right from the scratch. This post covers how to split column data into two.

Launch Excel 2010, Open datasheet, choose the column you want to split, right-click the column next to it, and on its context menu click Insert.

For Instance: We need to split the Name column. Right-click column C, and hit Insert.

column to slit 1

You will see new column is inserted.

new 'column

Now select the column you want to parse, go to Data tab and click Text to Columns.

data tab

You will reach a Convert Text To Columns wizard. In first step, you can choose the type of data. In this case we will  choose Delimited as data type. Click Next to continue.

text cnvrt wizard

In this step, you can set the delimiters that your data contains. Under Delimiters choose an appropriate option. In our case, space is the delimiter so we will choose space and enable the options which says Treat consecutive delimiters as one. Click Next.

step 2

Here you can select a column and set data format. We will select General under Column data format. From Destination we have already selected the column which we want to split, but you can also change the Destination by clicking button at the end of it’s field. In the bottom pane, you can view the changes that occur according to the settings. Click Finish to end the wizard.

step 3

Click OK to replace the contents of the destination.

replace dialog

Now you can see in the screenshot below, that the column is successfully parsed into two columns.

split complete' 1

You can also checkout How to freeze columns & rows in Excel 2010

Leave a comment