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

by Charley Kyd on September 8, 2009

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 used the following command…

…in New Excel: Data, Data Tools, Text to Columns.

…in Classic Excel: Data, Text to Columns

Then, in the dialog, 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.

Previous post:

Next post: