Copying Word tables into Excel without splitting cells into multiple rows

October 18, 2010 2:41 PM

I need help copying a table from Word to Excel in Office 2007. Where there are hard returns within a cell in the Word table, Excel splits the table cell into multiple rows. I need Excel not to split cells in the Word table into multiple Excel rows. At the same time, I need to retain the paragraphs that appear within the Word table cells - that is, I can't just get rid of all those hard returns; the formatting must be preserved. My Google-fu is failing me rather seriously. Is there any way to do this?
posted by Dasein to computers & internet (8 answers total)

What happens if you change the paragraph breaks to line breaks? (You can do this by pressing shift+enter instead of enter, or by doing a replace and replacing ^p with ^l (that's an L). I'm not positive it will work but it is definitely worth a shot.
posted by brainmouse at 2:47 PM on October 18, 2010 [1 favorite]

Maybe there is an elegant way to do this, but I'd resort to the quick and dirty technique of globally replacing all the hard returns in the word table with something like XX, and then globally replacing all the XX in the excel table with hard returns.
posted by found missing at 2:48 PM on October 18, 2010

brainmouse, strangely that doesn't seem to help. It's still splitting into new rows at each line break. Very strange. Your idea was a good one.
posted by Dasein at 2:53 PM on October 18, 2010

found missing: that works to get the data to paste properly into Excel (thank you!), but when I try to replace "xpqr" with ^p, it actually pastes in ^p, not a hard return. Do you know how to get Excel to paste in a hard return?
posted by Dasein at 3:00 PM on October 18, 2010

Yeah, so in the search and replace boxes, put "xpqr" in the find box, and in the replace box hold down Alt and type 0010 on the numeric keypad. You won't see anything appear in the box, but it will work magically.
posted by found missing at 3:11 PM on October 18, 2010 [1 favorite]

It's not an ideal method, but to replace the "xx" with a carriage return:

1) Select the range you want the replacements in.
2) Hit Alt-F11 to get into the VB editor
3) Hit Ctrl-G to get to the immediate window
4) Paste the following:

selection.replace "xx", chr(10)

and then hit enter to run it.
posted by pompomtom at 3:15 PM on October 18, 2010

*Did I ever tell you you're my HEEEERO?*

found missing, you are the best.

For future readers - I had to go back and insert a space before and after the "xpqr", otherwise the replace function in Excel wasn't finding it because it was attached to other text.

And in spite of this inferior Google Answer page leading me to believe that there was no way to insert a carriage return in the Replace function in Excel, found missing's trick worked perfectly.

1237. That's how many hard returns were screwing up the document. You saved some poor secretary a hellish night of manual input. Thank you!
posted by Dasein at 3:20 PM on October 18, 2010

But why are you bothering to use Excel for this anyway? Are you using any of the live calculation features? Or are you just using it for grid formatting?

If it's just formatting there are plenty of ways to do much the same thing in Word itself. No need to use Excel at all.

When all you know how to use is a hammer, everything looks like a nail...
posted by wkearney99 at 6:07 PM on October 21, 2010