Pasting text into Excel without splitting on spaces (or other delimiter)

Question

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?

mine is splitting url's on the colon... in every spreadsheet I use! — boomhauer, Jan 18 '14 at 19:54

score 55 · Accepted Answer · answered May 08 '12 at 20:34

55

This seemed to work, but is a bit involved.

Enter data into a cell.
Select the Text to Columns feature.
Make sure Delimited is selected and choose Next.
Uncheck the check next to Space (or the delimiter you want to disable)
Click Finish.

answered May 08 '12 at 20:34

Leigh Riffel

1,846
4
21
26

Don't forget to mark your answer as the solution @leigh-riffel. It helps the community. – evan.bovie May 21 '12 at 20:05
5

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. – Mohirl Jan 07 '15 at 11:28
argh. obvious (in hindsight) - thanks that's a great timesaver – fostandy Aug 15 '15 at 09:31
1

Sad UX!!.. They should have added an "Auto Split" option/checkbox button right next to the Split Text to Columns button. – WeSam Abdallah Nov 30 '16 at 18:29
FYI: TAB is default as far as I know – Joe Phillips Dec 07 '16 at 17:22
This is perfect! Essentially this is how we can reset excel back to its original configuration where text to column split would happen only on a tab by default (or else whatever you had set it to before). – shivesh suman Mar 01 '17 at 19:11

score 3 · Answer 2 · edited Jun 15 '15 at 14:37

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

edited Jun 15 '15 at 14:37

Leigh Riffel

1,846
4
21
26

answered Jun 11 '15 at 21:13

Kush Shah

31
1

1

Beware: running a macro loses your Undo history. – gws Dec 12 '16 at 19:46

score 0 · Answer 3 · answered Oct 27 '15 at 13:28

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)

answered Oct 27 '15 at 13:28

Rybob

11

1

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 Nov 11 '16 at 23:33

Pasting text into Excel without splitting on spaces (or other delimiter)

3 Answers3

Linked