Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Word-table-copy-to-excel-sample

Posted on 2011-02-14
6
Medium Priority
?
556 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 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

721 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