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

Advertisement
  • Carol Guinn

    Just what I needed. Thanks!

  • Laura Rice

    Just what I needed too, thank you :-)

  • Jr4821

    AWWWWW SNAP!!! Somebody just blew their boss’s mind…

  • Mahesh

    What if the cell contails a DDE link, then it shws the formula & not the test??????

  • Soujanya

    Hi,

    If we want to split one column into multiple rows…?
    something like if we have a+b, a-b ,a*b in one cell, now i want to place each operation in below rows..
    a+b
    a-b
    a*b

    what should i do?

    • e

      After splitting your text into cells, press F1 and look up “Switch rows and columns”.
      There is instruction for Copy / Paste-transpose.

  • Sweet_priya123

    Thank u so much for the post…..

  • Sam29681

    It is correct, but if there is samir more next colounm samir,more next colounm samir-more then which formula I use. 

  • kkamo

    hi can you split a cell horizontally?

    • BDT

      do it as per the above soln first, then transpose it.

  • http://www.facebook.com/nyeta.haines Nyeta Haines

    Thank you! Appreciate the screenshots!

  • Steph

    what if the data in the cell is from a string that has multiple lines – you know how you can have a hard return in an excel cell – how do I pull each row of data in one cell to a separate column

  • John

    What if there is not a space between the two items? For example, I have F54 representing Female 54 years old. I want the sex and age in two different columns.

  • Don Davis

    I missed a ProveIt exam question ‘split data into two cells using delimiter’. Now I know how.

  • noush

    didnt help me :(

    I want to know how to split one cell into two horizontally contiguous cells.
    And I want the first cell to have text, and the second cell to have a link.

    Ideally, I really want to put a link on part of the text in just one cell. (Although I know that’s not related to this article.)
    If you know how, please let me know.

    Thanks!

  • Kathy

    It was just what I needed to know. Thanks!

  • wort

    What if you have one cell with 1000+ first, last names and email addresses?

  • Shy Girl

    Microsoft 2010 sucks i want to split a column of cells into two cells and it
    cannot……. When you say split cell that is not what it means. What this video is showing is how to split your data and transfer it to another column that is not split cell split cell is splitting one single cell or more if you wish into two separate cells but it does not give you that option not any more this information is not what i am
    look for i know how to do this what i want is something like this:

    Name Download Watched

    The Chronicles of Narnia Y Y

    kasjf ksfksdfkjsafj askljjk fkas N N

    ksfjskj kasfaksfjaslkdfjask sd Y N

    and so on whatever …………. Y Y

    (Note: Above is my table
    or how my table is suppose to look now at the bottom below my table in
    column A i wish to put my key which will look something like this:

    Key

    Y Yes

    N No

    (Note:
    Column A is too wide for my key so i want to make the cells normal back
    to fit my key within a smaller table (without affecting the size of my
    table above) but i want all this under one Column which will be column
    A……. I use to be able to do this in Microsoft Excel 2007 now i can’t
    when i go in my ribbon to try and look for the icon to add it to my
    ribbon i see the icon is in the list of ribbons as it was in 2007 as
    Split cell but when i add this icon to my ribbon it does not work or
    give me the option to split the cells because it is not compatible with
    excel 2010 with that icon you use to be able to split just one single
    cell vertical, horizontal and diagonal now you cannot do so…….. That little icon
    use to make a whole lot of difference.

    If you know what i
    am talking about you will understand what i am showing you above…….
    If you know what i am talking about and know or figure out how to do
    this in Microsoft Excel 2010 please it would mean a great deal for me if
    you can let me in on the Information on how to do so.

    IF NOT MICROSOFT 2010 SUCKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    This is what split cell is this is what i am talking about!!!!!

  • Shy Girl

    the images i show you still really aren’t split cells but it was as close as i can find as to what i am talking about…. apparently no one really knew how to split cells

  • Shy Girl

    Ok here it is this is a perfect image of what Split cell is and there you have and image of the icon i am talking about……..

    • Shy Girl

      However this is in Microsoft word i couldn’t find one in excel but see the icon split cell button and if you notice he is still in column 2 and row 1and the person split that one cell in 4 parts that is split cell. Also when you use to click on that icon in excel it use to give you an u an option on whether you wish to split the entire column or from this point only or this cell only

      • aps

        Just merge the columns in excel that you want to display as one cell. for eg instead of splitting cells, just merge cells b1 and c1 into one column (column 1). b2 can be used to input ‘photos’, c2, b3, c3 can be adjoining columns. Merge b4 and c4 and u can input ‘dance’. merge b5 and c5 and you can input ‘cinema’. Simple

  • Melanie Diane Beltram

    This does not work very well with dates, if you can shed some light on how to do it properly with dates I would be very appreciative. Right now when I do it, it is taking the cell that I want to split, which is for example 1/10/14 and splitting into three cells that are cell 1: 1/1/1900; cell 2: 1/10/1900; cell 3: 2014. Totally blew my mind. I have a list of about 1,000 birthdays, that I would like to split into separate month, day, year cells. Please help if you can.

    • Stevie-Target

      Cell A1 = 24/01/2014.
      Make 3 new columns.
      In B1 type =TEXT(A1,”d”)
      In C1 type =TEXT(A1,”mmmm”)
      In D1 type =TEXT(A1,”yyyy”)
      For B1 (“d” = 9, 10 …. “dd” = 09, 10 …. “ddd” = fri …. “dddd” = friday …. “ddd dd” = fri 24) – there is no way for it to say 24th etc without using lookup for number)
      For C1 (“m” = 9, 10 …. “mm” …. “mmm” Jan …. “mmmm” January)
      For D1 (“y” / “yy” = 01 …. “yyy” / “yyyy” = 2001)
      You can copy these formula down the rows too.
      Play around once done select these columns copy, then paste as values (paste special)..
      Hopefully that will work for you.
      Stevie.