CODEWITHSUNDEEP
vbaexcel
Marco
Marco

Reputation: 95

Text to Columns delimitter

I'm trying to do Text to Columns with the OtherChar not just limited to one. Currently my code looks like this: 

With Selection
    .TextToColumns Destination:=[A2], _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=True, _
        Other:=True, _
        OtherChar:="-", _
     TrailingMinusNumbers:=True
End With

But I want the OtherChar to include these characters "(, ), [, ], /, *". Is there a way to do that?

Upvotes: 1

Views: 330

Answers (1)

Jules
Jules

Reputation: 1423

Quick and dirty code. Do not use * as delimiter as it will be treated as all in replace

Dim aDelim
Dim delim
Dim oRange As Range

aDelim = Split("(,),[,],/", ",")
Set oRange = Selection

For Each delim In aDelim
    oRange.Replace What:=delim, Replacement:="-"
    Debug.Print oRange.Text, delim
Next

oRange.TextToColumns Destination:=[A2], _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, _
    Tab:=True, _
    Semicolon:=False, _
    Comma:=True, _
    Space:=True, _
    Other:=True, _
    OtherChar:="-", _
 TrailingMinusNumbers:=True

Upvotes: 1

Related Questions