Separating text and numbers from a mixed code

**amahesh101** · 12-08-2010, 05:01 AM

Hello,

Need help to separate text and numbers from a alphanumeric code. the code could be in any order ( no first, no last, no in between etc).
Did try available solutions but yet far off from desirable outcome.

Ideally I was looking to get a formual based solution to the sample data enclosed here. I have been able to separate/solve part of it through text to col function but as it is, it will require an application of function everytime I have new data. A formula based solution could have helped permanently.

Will be very thankful if some body could help

regards

**pike** · 12-10-2010, 03:42 AM

Hi amahesh101

there is a pattern so you could run the text to column thrice

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True