Solved

Word-table-copy-to-excel-sample

Posted on 2011-02-14
6
551 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:
teylyn 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:teylyn
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 50

Expert Comment

by:teylyn
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:teylyn
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

705 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

11 Experts available now in Live!

Get 1:1 Help Now