Chris R writes in today to ask "Why does Excel sometimes automatically perform Text to Columns when pasting data from another application. In Episode #1383 Bill will explain under what conditions this will happen and how to turn it off or on.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1383: Disable auto text to columns.
Well hey welcome back to the MrExcel netcast, I'm Bill Jellen and today's question is sent in by Chris.
Chris is a little annoyed as I remember being annoyed when this used to happen to me all the time.
Chris noted that if he takes data from somewhere else, not Excel -- I'm just going to copy from notepad, this is a test, and come back here to your page in Excel, it all goes to one column right, beautiful.
Now we're going to go to Data, Text to Columns and it is delimited, delimited by space.
Press Finish and all right, it breaks it apart.
All right now this is the annoying part.
We're going to close this workbook, New one, notice I left Excel open.
There's a very strange thing that happens.
I'm going go back to Notepad and copy that data Ctrll+C. Come back to Excel and paste Ctrl+ V. Look at that.
It put it in separate columns.
Chris is understandably annoyed.
They didn't do it the first time but then all subsequent times it is doing it.
What's up with this?
Well, let me actually close Excel, don't save.
I'm going to go to a different Notepad window here and this one is tab delimited.
Ctrl+C and open Excel and paste that tab delimited there and check it out, it automatically gets broken out.
What is the story?
Why does it decide to break it out or not to break it out?
It all goes back to the Text to Columns dialog box.
When Excel opens step 2 has a Tab check mark.
Whatever is stored in this dialog box, it will automatically break out by that.
So Chris does Data Text to Columns, chooses space.
Now for the rest of the day until he closes Excel or until he knows another Text to Columns it automatically breaks it out based on spaces.
It’s very annoying that it didn't happen at first and then starts happening and actually there might be sometimes where you want it to happen and you can't figure out how to make it happen.
All right, well now you know it’s ever stored in step 2.
Chris says “well how do I stop that?” One way to stop it, the annoying way is to close Excel and re-open Excel between each paste.
Not sure I want to do that.
Another way is just come out here select a couple of cells then Text to Columns, delimited, go to step 2 and choose something else, like something you're not going to get like a tab or even you know a weird character or something you know is not going to be in your data and click Finish.
Now you can go back and you can even take in this case I should be able to go back to our Excel spaced data.
Paste and it all gets pasted into a single column.
All right so very frustrating because it seems to just randomly start while in fact didn't just randomly start its whatever happens to be stored in step 2 of the Text to Columns dialog box.
It’s still frustrating that it happens sometimes, not other times but at least now you know the rationale behind it, it’s easy to go into Text to Columns go to step two and change delimited, change to something else and then click finish in order to turn that behaviour off.
Is there a way to turn it off altogether?
I haven't found that but if anyone knows that of course shoot us a note at bill@MrExcel.com.
Hey I want to thank Chris for sending that question in and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel
Learn Excel from MrExcel podcast episode 1383: Disable auto text to columns.
Well hey welcome back to the MrExcel netcast, I'm Bill Jellen and today's question is sent in by Chris.
Chris is a little annoyed as I remember being annoyed when this used to happen to me all the time.
Chris noted that if he takes data from somewhere else, not Excel -- I'm just going to copy from notepad, this is a test, and come back here to your page in Excel, it all goes to one column right, beautiful.
Now we're going to go to Data, Text to Columns and it is delimited, delimited by space.
Press Finish and all right, it breaks it apart.
All right now this is the annoying part.
We're going to close this workbook, New one, notice I left Excel open.
There's a very strange thing that happens.
I'm going go back to Notepad and copy that data Ctrll+C. Come back to Excel and paste Ctrl+ V. Look at that.
It put it in separate columns.
Chris is understandably annoyed.
They didn't do it the first time but then all subsequent times it is doing it.
What's up with this?
Well, let me actually close Excel, don't save.
I'm going to go to a different Notepad window here and this one is tab delimited.
Ctrl+C and open Excel and paste that tab delimited there and check it out, it automatically gets broken out.
What is the story?
Why does it decide to break it out or not to break it out?
It all goes back to the Text to Columns dialog box.
When Excel opens step 2 has a Tab check mark.
Whatever is stored in this dialog box, it will automatically break out by that.
So Chris does Data Text to Columns, chooses space.
Now for the rest of the day until he closes Excel or until he knows another Text to Columns it automatically breaks it out based on spaces.
It’s very annoying that it didn't happen at first and then starts happening and actually there might be sometimes where you want it to happen and you can't figure out how to make it happen.
All right, well now you know it’s ever stored in step 2.
Chris says “well how do I stop that?” One way to stop it, the annoying way is to close Excel and re-open Excel between each paste.
Not sure I want to do that.
Another way is just come out here select a couple of cells then Text to Columns, delimited, go to step 2 and choose something else, like something you're not going to get like a tab or even you know a weird character or something you know is not going to be in your data and click Finish.
Now you can go back and you can even take in this case I should be able to go back to our Excel spaced data.
Paste and it all gets pasted into a single column.
All right so very frustrating because it seems to just randomly start while in fact didn't just randomly start its whatever happens to be stored in step 2 of the Text to Columns dialog box.
It’s still frustrating that it happens sometimes, not other times but at least now you know the rationale behind it, it’s easy to go into Text to Columns go to step two and change delimited, change to something else and then click finish in order to turn that behaviour off.
Is there a way to turn it off altogether?
I haven't found that but if anyone knows that of course shoot us a note at bill@MrExcel.com.
Hey I want to thank Chris for sending that question in and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel
