Clearing The Text To Columns Parameters
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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks