How to Change Text to Columns When You Copy and Paste in Excel

Here's how to split text into columns when you paste the text to your worksheet, and how to turn off this feature when you're done.

6949

Here's how to split text into columns when you paste the text to your worksheet, and how to turn off this feature when you're done.Today I needed to copy a bunch of numeric tables from a pdf file into Excel.

When I pasted the first table, all the data was pasted into column A, with a space between each number. That was no problem. I just selected the column of data and then, in my Ribbon’s Data, Data Tools group, I chose Text to Columns.

Then, in the Wizard, I chose Delimited, specified Space as the delimiter, and then chose OK.

If you work with text files much, you probably know all about this command. But here’s something you might not know…

When I pasted the next table, Excel automatically applied my current text-to-columns setting to the new data. That is, Excel didn’t paste the table into column A this time. Instead, it used each space in the table’s text as a delimiter and pasted the table into separate columns, just as I had specified using the initial Text to Columns command.

This method works with every delimiter I’ve ever tried, and with every recent version of Excel through Excel 2010. It works when you copy text, but not if you copy another cell. Also, this method does not split columns if you choose Fixed Width instead of Delimited.

But How Do You STOP Changing Text to Columns?!!!

We’re not done yet. This is because, once you’re done splitting text into columns, you need to be able to turn off that feature. I’ve found two ways to do it.

First, you can go back into the Text to Columns Wizard and change the setting to use an obscure delimiter, like the @ character.

Or, if that’s not obscure enough for you, you can use a Unicode character. Here’s an easy way to do it:

Select an empty cell; hold down your Alt key; then in your keyboard’s ten-key pad, type: 01234. That will give you the character Ò, which you can copy from your formula bar and paste to the wizard as your delimiter.

Second, you can reboot Excel.