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.
Search for Tips
Browse Tips by Category
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