Splitting comma delimited numbers into new columns

**salmansohail** · 05-30-2010, 06:28 PM

Hi,
I got a excel sheet with three columns and dynamic number of rows..

column(A) column(B) column(C) column(D)
123 "abc,1,2,3,4,b5" 23456 99999
123 "cde,3,4,b5" 23456 00000
123 "ghi,2,3,4,b5" 23456 00000

i am struggling to split COLUMN(b) text and move into new columns next to column(D).

Note: I got multiple excel files which are almost similar in nature but number of rows varies from file to file.

Please can someone help with somekind of macro or function, which i can automate this process.

Many thanks,
Sohail

**Dulanic** · 05-30-2010, 06:39 PM

Use text to columns, use space as your delimiter. Works fine for me.

Edit: Oh I misread what you said. Just use text to columns on B with , as the delimiter. Set the target as E1.

**salmansohail** · 05-30-2010, 06:42 PM

Yes i knew this is possible, but how can we do using within a module or say somekind of script, than attach it to a macro.

**Dulanic** · 05-30-2010, 06:44 PM

Use macro recorder....

It gives:

    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True

To always make it column B just change selection to Columns:

    Columns("B").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True

**salmansohail** · 05-30-2010, 06:57 PM

sorry i didn't get it. i think this is quite possible if i got excel 2003 or excel 2007, but unfortunatley for tonight i got open office calc and there is option in calc to specify destination column.
that is why i want some kind of split function and attach this to a macro/button.

but Many thanks