Convert Word table to Excel

[MarcLodge]

Hi All,
I have a table in Word that I'd like to move to Excel in order to do some totalling, and filtering etc. The table has got carriage returns in many of the rows so that one row can spreads over numerous lines on the written page.

What I would like to do is port this straight into Excel, but the problem I am having is whenever Excel finds a carriage return, it moves down a row in the worksheet. What I need is for the formatting of the table in Word to be applied to Excel ie. if a row in the table in Word is 4 lines deep, then I'd like the cell/row in Excel to carry the CRs across and only put it in one cell/row, not four.

Can you understand what I am trying to do, and is there an easy way to do it without re-typing?

Many thanks,

Marc

 

[SkipVought]

Hi,

In Word, replace the line feeds with spaces. The copy 'n' paste into Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MakeItSo]

Or, if you absolutely want to keep the line breaks, replace them in Word with a unique character combination like {|}, copy to Excel.
Unfortunately, you cannot replace with a special character like line feed in the standard Excel Replace dialog, so you'd have to do this with a little macro:

Sub MyReplace()
    Cells.Replace What:="{|}", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder:=xlByRows
End Sub

Note: This will not work if the cells contain more than 1024 characters! Excel tolerates cell volumes up to 32k, but it does not like them!

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MarcLodge]

Skip, Makeitso,

Two excellent suggestions that I just hadn't thought of. I shall be using either/both of them.

Many thanks

Marc