Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Ontario, Canada
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting Data in Cells (XP )

    Hey there

    I have a column where each cell contains data like this: 34 55
    This info has been pulled from another source.
    I would like to come up with a formula that will split these numbers for me so that 34 appears in one column and 55 in another. Currently they are only divided by a space.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Splitting Data in Cells (XP )

    The Data, Text-to-Columns wizard is usually more convenient that developing a formula.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting Data in Cells (XP )

    First, make sure that there are enough empty columns next to this column to hold all of the numbers in the longest set of values (if 34 55 is the longest, then you need two blank columns). You actually need one less empty columns that the max number of values, but I like to have a safety margin.

    Then select all of the cells containing these strings of values. Now, click on "Text to columns ..." in the data menu. In the dialog box click on delimited, then click on the Next button. If there is not a check mark next to Space, click on it to check it (you can remove any other check marks it they might cause a problem, but you might also want one next to Tab). Click on Finish. Your data should now be split into separate cells.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting Data in Cells (XP )

    In addition to Legare's post, in the TextToColumns wizard, I change the destination cell, so that the first number does not overwrite the original. This means you can inspect it and confirm that the translation has worked, and then delete the original column.
    Ruth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •