# Copying Word table into Excel



## kalavinod (Nov 18, 2005)

I am trying to copy Word table into Excel (Office 2003).

When a cell in Word table contains two sentences with one blank line in between, it copies into three cells in Excel. The first sentence, blank cell, second sentence.

How can I get to copy one Word table cell into one Excel cell? I want to copy the entire table, not one cell at a time. I know how to do it one cell at a time.


----------



## yustr (Sep 27, 2004)

Try this: Copy the Word table and in Excel right click on the upper-left most cell and use Paste Special>Microsoft Office Word Document Object.


----------



## kalavinod (Nov 18, 2005)

That is not what I want.

Let us say in Word I have table 5 rows and 3 columns. That's 15 cells total.

When I copy and paste to Excel, I want to copy into 5 rows and 3 columns.

The problem is if one of the cells has more than 1 sentence separated by a blank line, then instead of pasting it into 1 cell in Excel, it pastes into 3 cells.

For example, below is into one cell in Word table. I want to copy that into 1 cell in Excel. If it was only one cell, then I can paste it into the formula bar and it does what I want. But I do not want to copy and paste one cell at a time. It will take too long. I have a table that is over 10o rows by 7 columns.

Calculated as:
If date falls between {#82, First Floor Start Date} and {#83, First Floor End Date})
Then = {#809 Amended 1988 Accord Risk-weighted Assets} x 0.90

if date falls between {#84, Second Floor Start Date} and {#85, Second Floor End Date}) 
Then = {#809 Amended 1988 Accord Risk-weighted Assets x 0.80}

If date falls outside of {#82, First Floor Start Date} and {#85, Second Floor End Date}
Then = 0


----------



## Glaswegian (Sep 16, 2005)

Hi

There is no easy way to do this.

Either you have to clean up your Word table to ensure the carriage returns are deleted, or copy to Excel and then use a macro to remove the blank lines. Post back if you need a hand with an Excel macro for this.

Regards


----------



## kalavinod (Nov 18, 2005)

Iain: Thanks. Would appreciate your help with excel macro. When I paste to Excel, non-blank cell in column A will determine the beginning of row in Word table. So all the rows between non-blank value in column A should collapse to one row in Excel. In the following example rows 1-4 should collapse, and rows 5-8 should collapse. Let me know if there is a way to attach word or excel attachmentshere.

Col A col B Col C

Row 1 101 first line First Line
Row 2 First Line Cont 
Row 3 Second Line
Row 4 Third Line
Row 5 102 first line First Line
Row 6 First Line Cont Second Line
Row 7 First Line Cont 
Row 8 Third Line Third Line


----------



## Glaswegian (Sep 16, 2005)

Hi Kalavinod

Based on you first couple of posts, I understood that the result in Excel would leave a blank line every second line. Is that correct?

Try copying a sample of your data, perhaps a dozen entries or so, *making sure you remove any sensitive info*, and then use the 'Manage Attachments' button further down the Reply page to upload the file.

I appreciate that sometimes it can be difficult to follow things without seeing the actual file. *You* know what you mean, but *I* need to know as well. :grin:


----------



## kalavinod (Nov 18, 2005)

Sorry, Iain. My text did not come out correct. But you get an idea. One row in Word may consume 5 rows in Excel when pasted, depending on number of cariage returns in any given cell. These 5 rows in Excel should collapse back to one row when blank cells are removed. Non-blank cell in first column determines the beginning of a new row. Your help will be much appreciated.


----------



## kalavinod (Nov 18, 2005)

Iain: 

See the Word attachment. No it would not necessarily mean every second line will be blank. Each cell in Word table row may have a varying number of CRs.

I work in the bank. I found the caption "A bank will loan you..." very true.


----------



## Glaswegian (Sep 16, 2005)

No worries :grin: We'll get there (one way or another :grin: )

OK, so all I'm looking to do is delete blank rows, yes? Now, if a cell in colunm A is blank does this mean that every other cell in the same row is also blank?

I just want to make sure. :smile:


----------



## kalavinod (Nov 18, 2005)

Iain: See if Excel attachment from Word table makes sense. Row 1-10 should collapse into one row. Rows 11-15 should collapse into one row.


----------



## Glaswegian (Sep 16, 2005)

Kalavinod

Give me some time with this. We have the dreaded merged cells here! (always a bu88er when coding in Excel).

I'll get back to you asap.


----------



## Glaswegian (Sep 16, 2005)

Kalavinod

This code will unmerge any cells and simply delete any blank cells and move the remainder up. It does what you asked for, but I'm not entirely sure the outcome is what you're expecting (which is why I took so long :smile: )


```
Sub DemergeCells()

With ActiveSheet.UsedRange
    .MergeCells = False
    .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With
End Sub
```
Test with a copy sheet and see what happens.


----------



## kalavinod (Nov 18, 2005)

Iain: Pls see the attached "Expected Excel Output". At the end, I want to end up with similar look as the Word document we started with. If the blank lines within the cells are not there, it would be ok. The driving factor is a non-blank value in col A, this makes a beginning of a new row.


----------



## Chevy (Jul 25, 2003)

Ok ... $.02 alert.

Are the results the exact same every time? That is, word shows row 1 as A1,B1,C1. But, excel makes it A1,B1,C1,D1? (where C1 and D1 should be just C1)

If this is the case, set up sheet1 to be your "final" product, but import into sheet2.

Sheet1 values:

A1: =sheet2.a1

B1: =sheet2.b1

C1: =sheet2.c1&sheet2.d1

If you want, alter the values with an =IF statement so that you can ignore empty cells.

On sheet1, highlight A1 through C1, then grab the little box in the bottom right corner of the selected area and drag down as many rowas as you'd need.


----------



## kalavinod (Nov 18, 2005)

Chevy:
No, my problem is it keeps the same number of columns, but it pastes each Word table row into several Excel rows depending on how many carriage returns are in Word cells.





Chevy said:


> Ok ... $.02 alert.
> 
> Are the results the exact same every time? That is, word shows row 1 as A1,B1,C1. But, excel makes it A1,B1,C1,D1? (where C1 and D1 should be just C1)
> 
> ...


----------



## Glaswegian (Sep 16, 2005)

Hi Kalavinod

I don't think there's any easy way to do this. Looking at it again, based on the sample sheet you posted, the problem is you need to concatenate several cells, but not always the same number of cells in each column and include carriage returns as well.

I reckon you should perhaps think about amending your Word table to a style that will easily import into Excel.

Perhaps an obvious question I missed - why are you importing this into Excel? Do you need to do something with the data? Can it not be done in Word?

I know that's probably not much help but it's all I can come up with at this time.

Regards


----------



## kalavinod (Nov 18, 2005)

Iain:
I appreciate your help. The reason I want to bring the info into Excel is so that I can do the filtering and sorting. This sounds so basic, I wonder why my friend Bill (as in Gates) didn't think about this feature to copy Word table into Excel cell by cell intact.
Now that you know what result I am looking for, may be you can keep your thinking cap on for a while longer.
Thanks again for your willingness to help.


----------



## Glaswegian (Sep 16, 2005)

Kalavinod

See if this would get you started in some way

http://www.officearticles.com/word/sorting_data_in_microsoft_word.htm

I still think we need to work on the data in Word before we bring it into Excel.

Regards


----------



## Chevy (Jul 25, 2003)

kalavinod said:


> Chevy:
> No, my problem is it keeps the same number of columns, but it pastes each Word table row into several Excel rows depending on how many carriage returns are in Word cells.



But my solution seemed so nice! :grin:

The issue is between hard returns and soft returns.

How much data are we talking right now?

If the folks who enter data intot he Word document are trainable, have them use shift+enter instead of enter for paragraph breaks. Excel should interpret these "soft returns" correctly.


----------



## kalavinod (Nov 18, 2005)

Chevy:
Tried shift+enter to break a paragraph. Word to Excel behaves the same way. May be I am doing something wrong. Currently, there are a couple hundred rows of Word table. But there are several tables that I will be working with.



Chevy said:


> But my solution seemed so nice! :grin:
> 
> The issue is between hard returns and soft returns.
> 
> ...


----------



## kalavinod (Nov 18, 2005)

Iain: 
See attached logic for macro and see if you can develop a macro that will work on Excel created from Word table.





Glaswegian said:


> Kalavinod
> 
> See if this would get you started in some way
> 
> ...


----------



## kalavinod (Nov 18, 2005)

Iain:
You have gone quiet on me. Hope you are still working on the macro for which I sent you the logic. I need help.


----------



## Glaswegian (Sep 16, 2005)

Hi Kalavinod

Let's try this. I got the idea after reading the Word article in the link I posted earlier. So, the first macro is for Word and it replaces the carriage returns in your table with the '@' symbol.

```
Sub ReplaceReturns() 'Word Macro
Selection.Tables(1).Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
    .Text = "^p"
    .Replacement.Text = "@"
    .Forward = True
    .Wrap = wdFindStop
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
```
Probably uses 'Selection' a bit too much but I'm not really familiar with the Word Object Model.

Now copy and paste to Excel. Then run this Excel macro to put the carriage returns back

```
Sub InsertReturns() 'Excel Macro
Dim str As String
Dim cell As Range
  
For Each cell In ActiveSheet.UsedRange
    str = cell.Value
    cell.Value = Replace(str, "@", Chr(10))
Next
End Sub
```
All the data that was in one cell in the Word table should now be in one cell in Excel.

Ideally, I should really do it all from Word, i.e. the copy, paste etc but I'm not that good at automating between MS applications.

Try this and let me know how it goes.

Regards


----------



## kalavinod (Nov 18, 2005)

Iain:

This works like a charm. I saw your enthusiasm to help and I knew you would come through. Sorry, I was inactive over the weekend, so didn't try this earlier.

To be on the safe side, the only change I made is actually use two @ characters instead of one. The reason being that just in case one @ character is used within the original text, I don't want to replace it with Return.

I am from the old school, meaning, started in programming 30 years ago and I have moved away from it many years ago. So I only know procedural way of doing things which may get the job done but often not very efficiently.

Thanks once again. Now the whole word can get around the similar problem.


----------



## Glaswegian (Sep 16, 2005)

You're welcome.

I guess you could really use any symbol as long as it is not likely to appear in the text somewhere - I just chose the '@' because it's useful in some formulae for substituting for a space. Just habit really.

Regards


----------

