Solved

Word-table-copy-to-excel-sample

Posted on 2011-02-14
6
552 Views
Last Modified: 2012-05-11
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...
     Word-table-copy-to-excel-sample.docx
0
Comment
Question by:Andross9
  • 4
6 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 34886810
Hello,

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
0
 
LVL 4

Expert Comment

by:DangerousJeff
ID: 34886824
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.

DJ
Word-table-copied-from-excel.xls
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34886828
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.

Book4.xlsx
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34886851
@DangerousJeff

>> 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
0
 

Author Closing Comment

by:Andross9
ID: 34886882
Thanks Teylyn

Clunky but works

Appreciate the help..

Andross9
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34886900
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now