Solved

Word-table-copy-to-excel-sample

Posted on 2011-02-14
6
555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 50
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
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

627 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