MS Word

Transfer Data from Word to Excel

The Problem:

Every now and then, I receive data in a Word table that I need to paste into an Excel spreadsheet. Each time I do this, I seem to get different results: sometimes the text just snaps into the spreadsheet cells perfectly; other times, I get Word's table borders and font formatting in my spreadsheet, andworsesome table cells are spread across multiple rows. If I undo this unsuccessful paste and repeat it, I get different results again. If it makes any difference, this is Word 2003 I'm using.

The Solution:

Word is trying to be helpful, but it's not quite managing what you want. Word 2003 tends to behave as you describe; Word XP and Word 2000 behave a little differently (and Word 2000 doesn't have the Paste Options Smart Tag).

Here's what's probably happening:

  • If the table you copy has only one paragraph to each cell, when you paste, the data snaps into place with one Word table cell to one Excel cell. This is probably the result you want.

  • If the table contains cells that have two or more paragraphs in them, Word pastes in the table as a Word object by default. That means the that table keeps its Word formatting, so you get the text formatting and borders, and multiparagraph cells end up occupying multiple rows (one row per paragraph). You can fix this quickly: click the Smart Tag button and choose Match Destination Formatting to redo the paste operation as plain text. The multiparagraph cells will still occupy one row per paragraph, so you'll need to fix them manually, but Word's formatting will be gone.

  • If you undo the paste operation and then repeat it, Word may say to itself (in binary) "Oh, she didn't like what I did; I'll do it the other way this time" and paste in plain text instead of the Word object. This will give you the effect described in the previous paragraph, but it doesn't happen consistently (and you're not allowed to ask me why).

If this degree of uncertainty annoys you, use Paste Special instead: right-click the upper-left destination cell, choose Paste Special from the shortcut menu, choose the Unicode Text option in the As list, and click the OK button.