Text-to-Columns Delimiter Based on Cell Contents

[lneidorf]

Hi there.

I frequently review DAT files. These DAT files use a variety of characters as delimiters. I'm trying to devise a macro that will perform a text-to-column operation, using the content of a specified cell as the delimiter.

Here's my code, which uses þ (ASCII 0254) as a delimiter:

With Sheet1
    Range("A1", ActiveCell.End(xlDown)).Select
    
    Do
    Selection.TextToColumns Destination:=ActiveCell.Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="þ", FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Loop Until IsEmpty(ActiveCell.Columns("A:A"))
    
    Dim i As Long
    For i = 255 To 1 Step -2
    Columns(i).Delete
    Next
    
    Range("A1", Selection.End(xlToRight)).Select
    Selection.Columns.AutoFit
    
    Range("A2").Select
    ActiveWindow.FreezePanes = True
End With

Instead of having the delimiter hard-coded, I'd like my macro to use whatever character I enter into Sheet3.Range("i5") as a delimiter. That way, I can easily change my delimiter character as necessary.

Any suggestions would be most appreciated.

Thanks!

 

[Sal Paradise]

The key is here:

    Selection.TextToColumns Destination:=ActiveCell.Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="þ", FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

The "OtherChar:=" portion is followed by the character you use to split the data. So if you make a variable equal to Sheet3.Range("i5") and then use that variable after the OtherChar:= portion, it should use that as the delimiter.

Sample:

Dim strDelimiter As String

strDelimiter = Sheet3.Range("i5")

    Selection.TextToColumns Destination:=ActiveCell.Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=strDelimiter, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

 

[buxtongt]

One small tweek, don't forget to define the range before the text to columns code, with something like:

Range("a1").Select

where a1 is the cell you want to perform the text to columns function on.

 

[lneidorf]

That did the trick. I think I just learned something to boot.

Thanks very much!

 

[Sal Paradise]

Thanks for the feedback, and glad you got it working.