Split Numbers In Single Cell Across Columns

  • I have in cell (A1) 3 7 10 (These are separated by a space)
    and in (B1) 2 4 11 23 (These are separated by a space also)

    What I need is in Column (B)
    (B1) 3
    (B2) 7
    (B3) 10

    In Column (C)
    (C1) 2
    (C2) 4
    (C3) 11
    (C4) 23

  • Re: Extract multiple numbers from a cell separated by spaces


    To do it manualy:
    Select A1
    Data> Text to Column> Delimited > Space
    Then
    Copy A1:C1
    Edit > Paste Special > Transpose
    Is it enough?

    Triumph without peril brings no glory: Just try

  • Re: Extract multiple numbers from a cell separated by spaces


    Try this code... it will make a column out of a the contents of the currently selected cell (assuming the contents of the currently selected cell are seperated by a space).



    Regards,
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Split Numbers In Single Cell Across Columns


    Great! Almost there.

    I have numerous cells (over 50) to separate in Column(A) and put in column (C)
    The cell to start on is (A1) and move the results to (C1) then get next data cell in (A2) repeat above.

    Skip a line before posting the next results.

    It would help if in Column(B) it had a reference number for each separation. Example all seperations from cell(A1) would be identified as "A1" in Column(B)
    next separation would be "A2".

    This would allow me to use a pivot table on the data.

    Thanks for your help

  • Re: Split Numbers In Single Cell Across Columns


    This seems to work:



    Have a good weekend.
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Split Numbers In Single Cell Across Columns


    LOL ::D


    Thanks, I'm on a lucky streak today.


    Cheers
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Split Numbers In Single Cell Across Columns


    Take a look again at the cell ID.

    From (A1) It shows A2, then corrects to A1
    Next from (A2) shows A0, then shows A1
    Next from(A3) shows A2, then corrects to A3

  • Re: Split Numbers In Single Cell Across Columns


    Also

    Would like the new data to start at the top row in Columns (B1) and (C1). It now starts on the row below the last data entry

    Thanks,

  • Re: Split Numbers In Single Cell Across Columns


    Hi John, its not clear to me what problem you are having with the ID's. See attached, where the ID's are fine. I update the code to show the data starting on Row 1. I also removed the formulas.



    Ger

    Files

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Split Numbers In Single Cell Across Columns


    @comments to code


    Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    is probably equal to
    [COLOR="royalblue"]Range("A1").currentregion.columns(1)[/COLOR]
    or
    [COLOR="royalblue"]usedrange.columns(1)[/COLOR]


    Range("$B:$C").ClearContents is unnecessary in VBA, because Range("B:C") is always fixed.


    Split(my_cell.Value, " ")) can be replaced by Split(my_cell.Value) because space is the default delimiter


    the loop can be rewritten as

    Code
    For Each cl In my_range
        cells( Rows.Count,3).End(xlUp).Offset(2).Resize(UBound(split(cl))) = Application.Transpose(Split(cl))
    Next
  • Re: Split Numbers In Single Cell Across Columns


    Hi SNB


    @comments to @comments to code ::D


    Different way of saying the same story with no tangible imrpovement on performance IMHO. ;)


    In fact, I dont like this -

    Code
    cells( Rows.Count,3).End(xlUp).Offset(2).Resize(UBound(split(cl))) = Application.Transpose(Split(cl))

    , because you are splitting the array twice, whereas I only split it once. If it was large array, it would be a performance hit against your code.


    And as for split with the default value - its risky when dealing with different office versions (03/07/10) in different regions (US/UK/FR/DE). By force of habbit I NEVER rely in defaults with Excel. It's no extra weight to carry for that extra bit of insurance. Just my tupence worth anyway ;)


    Cheers
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Split Numbers In Single Cell Across Columns


    I have in cell (A1) "001 - 002, 004 - 006, 008" (These are separated by a space)
    and insert entire row and paste in column wise.


    Input:
    A1 Cells "001 - 002, 004 - 006, 008"


    Output:
    Cells insert and paste below data


    A1 Cells 001
    A2 Cells 002
    A3 Cells 004
    A4 Cells 005
    A5 Cells 006
    A6 Cells 008

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!