Splitting comma delimited numbers into new columns

**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