I have created this 110 page MS Word 2007 table.

Each cell has a some text with hard returns in it.

I tried to copy the word table and paste it into excel, but wherever there is a hard return, the text goes to the next cell in excel and the formatting gets screwed up.

I already tried copying the whole table and pasted into excel and tried the paste options thing and chose "Keep original formatting".

Neither of these worked...

Is there a VBA program or something out there that I can use?

Any other ideas/suggestions are welcome, I am pulling my hair out trying to figure this out...
Who is Participating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:

this is a bit tricky, but if you're prepared to use a workaround, read on.

In the Word document, select the table, then use the Find and Replace dialog to replace all paragraph marks with a character string that does not occur elsewhere in the table, e.e. ###

Find and Replace > Find what > Special button > Paragraph Mark
                               Replace with > ###

Click Replace All.

Now copy the table and paste it into Excel.

The table will still be selected in Excel after the paste. With the pasted table still selected, use Ctrl-H to open the Find/Replace dialog in Excel and enter

Find what -- ###
Replace with -- <hold down the Alt key> and type 0010 <on the number pad of your keyboard>

Click Replace All

Now the Excel table will have "soft" line breaks in the cells, the same line break you would normally achieve with Alt-Enter when typing into an Excel cell.

Does that help?

cheers, teylyn
Just a straight select table, copy and paste worked fine for me... I was using open office though.
Then need to activate the wrap automatically option on the cells and possibly the optimal row height.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The result of the procedure described above, based on your sample Word file, is attached.

I've formatted the column widths and row heights to something readable.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

>> I was using open office though

So you don't really know how the scenario unfolds with Microsoft Office 2007, do you ?  I can assure you that there is a difference. OO and MS Office are not the same. OO is often better, but in this case, Andross9 is using MS Office, so needs a solution that applies to the tools on hand.

cheers, teylyn
Andross9Author Commented:
Thanks Teylyn

Clunky but works

Appreciate the help..

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the grade.

If you want "elegant" instead of "clunky", apparently Open Office is the way to go.

Let's see where MS Office is in 5 years' time.

Anyone doing the books?

cheers, teylyn
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.