36

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?

Improve this question
1
  • mine is splitting url's on the colon... in every spreadsheet I use!
    – boomhauer
    Commented Jan 18, 2014 at 19:54

3 Answers 3

Reset to default
58

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.
Improve this answer
7
  • Don't forget to mark your answer as the solution @leigh-riffel. It helps the community.
    – evan.bovie
    Commented May 21, 2012 at 20:05
  • 5
    this worked. so it appears the last text-to-columns operation is used for all future pasting.
    – boomhauer
    Commented Jan 18, 2014 at 19:55
  • It's used for all future pasting in any workbook, and also set by default whenever you open a text file.
    – Mohirl
    Commented Jan 7, 2015 at 11:28
  • argh. obvious (in hindsight) - thanks that's a great timesaver
    – fostandy
    Commented Aug 15, 2015 at 9:31
  • 2
    Sad UX!!.. They should have added an "Auto Split" option/checkbox button right next to the Split Text to Columns button.
    – WeSam Abdallah
    Commented Nov 30, 2016 at 18:29
3

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
Improve this answer
3
  • 1
    Beware: running a macro loses your Undo history.
    – gws
    Commented Dec 12, 2016 at 19:46
  • This only works is there's already data in the range of the selection. To make this work, i added a bit that sets a value (a string with a single space char), and wrapped a copy/paste to/from memory around it. See my next comment.
    – ikku100
    Commented Apr 3 at 9:54
  • ` Sub DisableAutoSplitting() Dim v As Variant ActiveSheet.Range("a1").Select v = Selection.Value Selection.Value = " " Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True ActiveSheet.Range("a1").Select Selection.Value = v End Sub `
    – ikku100
    Commented Apr 3 at 9:54
0

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)

Improve this answer
1
  • 2
    This works if you're trying to paste into only one cell. It doesn't work for pasting into multiple rows because all the lines end up in the one cell.
    – krubo
    Commented Nov 11, 2016 at 23:33

You must log in to answer this question.

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