Copying a table from Word

[BenO]

Hello,

I am trying to copy a table from Word into Excel. The cells contain mostly
text. Excel Help says to select the table cells in Word and the "copy"
command , then select the top left cell in Excel and use the "paste" command
- very simple.

However, the data is not pasted fromcell to cell: Many of the cells in the
word table contain paragraph breaks. Excel separates data from a single Word
cell into multiple cells based on these paragraph breaks.

Is there any way to get Excel to ignore the paragraph breaks within the text
and simple paste everything from the Word table cell into a since Excel cell?

Thanks.

 

[Sheeloo]

I am not aware of any inbuilt method...

What I do is something like the following (which you may have to play around
with to get it right);
In Word
Replace paragraph marks with something which is not likely to be in my cell
values like ~ or $...
Copy the table and paste in Excel... This avoids the splits into multiple
cells..
In Excel I use the formula in adjacent col
=SUBSTITUTE(B45,"$",CHAR(10))

Then format the cells with 'World Wrap'...
Once everything is the way I want I do Copy/Paste Special|Values...

Another way which sometimes meets the requirement is to insert a dummy col
in word table with one numbers or something like ROW.
This splits (for a row) the cell with max lines, say 10 into 10 cells and
merges the 10 cells (for the dummy cell) in the same row...

 

[BenO]

Thanks, I'll try it!

I am not aware of any inbuilt method...

What I do is something like the following (which you may have to play around
with to get it right);
In Word
Replace paragraph marks with something which is not likely to be in my cell
values like ~ or $...
Copy the table and paste in Excel... This avoids the splits into multiple
cells..
In Excel I use the formula in adjacent col
=SUBSTITUTE(B45,"$",CHAR(10))

Then format the cells with 'World Wrap'...
Once everything is the way I want I do Copy/Paste Special|Values...

Another way which sometimes meets the requirement is to insert a dummy col
in word table with one numbers or something like ROW.
This splits (for a row) the cell with max lines, say 10 into 10 cells and
merges the 10 cells (for the dummy cell) in the same row...

 

[Dave Peterson]

Saved from a previous post:

When I want to transfer a word table to excel, I do this:

If your cells in your word table contain paragraph mark or linebreak characters,
then excel will bring them over as separate cells.

One way around it is to convert those paragraph marks & linebreaks to unique
characters, then copy|paste and then convert them back to linefeeds.

I like this technique (inside a copy of the word file):
Select your table.
Edit|replace|Special (show More if required)
Find what: (paragraph mark under Special button)
replace with: $$$$$ (if $$$$$ doesn't appear in the table)
replace all

Same thing with Manual Line break (from under Special).

Now copy the table into Excel.

Edit|Replace
Replace what: $$$$$
Replace with: ctrl-j (hit and hold the control key and hit j)
replace all.

You may have to use Format|cells|Alignment tab|check wrap text

Don't forget to close the word document without saving (or hit undo as many
times as necessary).

 

[BenO]

Thanks for your help Dave. I was successfully able to transfer the data into
the correct cells, but I encountered another problem: When I replace the
$$$$$ with the ctrl-j, I lose all of my text formatting. Some of the text I
copied is underlined, some strikethrough, etc. All of the formatting is
there when I paste, but it is gone once I use the replace routine to
re-insert my paragraph breaks.

Any further suggestions?

I appreciate your help.

Ben

 

[Dave Peterson]

That's one of the problems with the edit|Replace.

The only solution I know is to have a macro that loops through each cell and
replaces the unique string with the vblf and applies the formatting to each
character (character by character).

Or leave the data in MSWord???