Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I recently used the Text to Columns feature in Excel to split a column of data into multiple columns based on the space character. Now when I paste text into Excel it automatically splits it into multiple columns without using the Text to Columns feature. Is there a way to revert this functionality back to normal?

share|improve this question
mine is splitting url's on the colon... in every spreadsheet I use! – boomhauer Jan 18 '14 at 19:54
up vote 23 down vote accepted

This seemed to work, but is a bit involved.

  1. Enter data into a cell.
  2. Select the Text to Columns feature.
  3. Make sure Delimited is selected and choose Next.
  4. Uncheck the check next to Space (or the delimiter you want to disable)
  5. Click Finish.
share|improve this answer
Don't forget to mark your answer as the solution @leigh-riffel. It helps the community. – evan.bovie May 21 '12 at 20:05
this worked. so it appears the last text-to-columns operation is used for all future pasting. – boomhauer Jan 18 '14 at 19:55
It's used for all future pasting in any workbook, and also set by default whenever you open a text file. – Mick O'Hea Jan 7 '15 at 11:28
argh. obvious (in hindsight) - thanks that's a great timesaver – fostandy Aug 15 '15 at 9:31

I created a quick Macro and added it to my personal.xlsm file. Here is the code. This more or less does the same thing that Leigh Riffel has suggested except it is a quick Macro run.

Sub DisableAutoSplitting()
    Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
End Sub
share|improve this answer

Wouldn't CTRL Z for undo be a faster way to revert it?

share|improve this answer
Undo reverts not only the implicit split, but also the explicit paste itself. I want the paste, just not the split. – Leigh Riffel May 21 '12 at 18:58

Or just click into the formula bar or into the cell as if you are typing, then paste - it will then only go into that cell. (LOL creating a macro - so over complicated)

share|improve this answer

You must log in to answer this question.

protected by Community Apr 20 at 10:09

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged .