If you are on a version of Excel that does not contain "Get External Data" under Data menu (such as Excel 2003, which is my primary version due to the anti-productive "upgrades" of Office 2007 through 2013), you can prevent the "helpful" behavior they force on you either by a few manual steps or by a macro (a macro is faster & easier, so I put it in my personal.xls):
- Add a workbook (or Ctrl+N)
- Type the letter A in cell
- Click cell
- Start “Text to Columns” (or Alt+D, E)
- Choose delimited (or Alt+D)
- Hit Enter
- Unselect every checkbox
- Click Finish (or Alt+F)
- Close the workbook; do not save.
Now the "helpful" behavior will be prevented when you paste again.
' Differs from Walkenbach in that he just populated A1 if empty, did this on A1, restored A1.
' This is better IFF you are allowed to add a workbook.
If ActiveSheet.ProtectContents Then MsgBox _
"BTW, the active sheet is protected. Consider unprotecting 'ere TextToColumns"
Cells(1) = "A" 'Required, else TextToColumns will error
Cells(1).TextToColumns DataType:=xlDelimited, Tab:=False, semicolon:=False, _
comma:=False, Space:=False, other:=False
FWIW, I wrote this routine independently, and then discovered
that Excel guru / author / developer John Walkenbach
had published something similar on his site, The Spreadsheet Page,
titled Clearing The Text To Columns Parameters.
See How do I add VBA in MS Office? for general information.