Clearing The Text To Columns Parameters

Category: General / General VBA | [Item URL]

Have you ever imported a CSV file, or pasted data into a worksheet, only to find that Excel split up your data incorrectly? If so, the culprit is probably the Text To Columns feature. Here's Step 2 of the wizard that's used to split a single column of delimited data into multiple columns.

In this case, three delimiters are specified: tab, comma, and colon.

This is a very useful feature, and I use it a lot. The problem is, Excel tries to be helpful by remembering these settings for subsequent CSV imports and paste operations. Sometimes remembering these settings really is helpful, but often, it's not. To clear these delimiters, you must display this dialog box, clear the settings, and click Cancel.

If you're importing or pasting via a macro, there's no direct way for your macro to check these settings or reset them. The solution is to "fake" a text-to-columns operation. The procedure below does that, with the effect of clearing all of the settings from the Text To Columns dialog box (and making no changes to your workbook).

Sub ClearTextToColumns()
    On Error Resume Next
    If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
    Range("A1").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        OtherChar:=""
    If Range("A1") = "XYZZY" Then Range("A1") = ""
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

This macro assumes that a worksheet is active, and it's not protected. Note that the contents of cell A1 will not be modified because no operations are specified for the TextToColumns method.

If cell A1 is empty, the code inserts a temporary string (because the TextToColumns method will fail if the cell is empty). Before ending, the procedure deletes the temporary string.