I'm pasting data separated by tab, usually the excel would be a "little" cleaver and separate the data by columns at each tab character.

I don't know why, but Excel doesn't recognize the tabs anymore and I have to use the text importing tools every single time!

It is odd that when I copy data from excel to notepad, the columns are separated by tab!! But if I try to paste it back to excel, the data goes to the same columns!

It is a frustrating and very dumb behavior of excel!

Why it used to work before? Older excel version was clever than the new one or what? What can I do to fix it?

Edit: it seams that it can happen due to some excel bug. After a reboot the \t was working again. It happened another 2 or 3 times.

  • I just tested this in 2010 and it works as expected (tabs separate into different columns). I don't have 2013 installed on this computer but will test that out later to see if I can figure out the difference. – Brad Patton May 29 '13 at 17:13
  • Are you sure they're tabs, and not just (4) spaces? – Ƭᴇcʜιᴇ007 May 29 '13 at 17:37
  • Yes I'm sure. I've even copied two columns from excel, pasted it to notepad and they are tab separated. After that I tried to paste it back from notepad to excel, but the data went to the same column. – Pedro77 May 29 '13 at 17:50
  • By into the same column, do you mean that the data was pasted into one column, with each of the data rows a string of text with the tabs embedded? If so, you should make sure that the delimiter for Text-to-Columns is set to Tab. – chuff May 29 '13 at 18:32
  • 2
    Select Text to Columns from the Data tab of the ribbon. Then choose the Delimited option in the first dialog box, check the Tab option in the second box, and click Finish. Then try the paste again. – chuff May 29 '13 at 21:50
up vote 9 down vote accepted

It sounds like you may have recently used the "Text to Columns" function and changed the delimiter to commas or spaces or something.

When you do that, Excel continues looking for the new delimiter in whatever you paste in. To revert to the old behavior, just go back into "Text to Columns" and change the delimiter back to Tabs.

  • 2
    Excel really needs to pop a dialog asking how to import pasted data, especially if it is a large set of data from something like a SQL query. I spent a long time trying to figure this out, and your answer was the solution. Thanks. – JYelton Jul 23 '15 at 23:07

This issue occurred for me after I ran a macro that imported data using the "|" character as the column delimiter. Even though I had closed the sheet that ran that macro, and closed all the sheets created by the macro, my other sheets open while the macro ran defaulted to "|" as the delimiter as well as any newly opened sheet. I solved this by closing all Excel sheets. Once I did that and then opened Excel, any newly opened sheet (blank or otherwise) again defaulted to using TAB as the delimiter. This was in Excel 2013.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.