Is there a way to move data from a Word table to an Excel sheet 'cell-for-cell'?

[kissenger]

This is a little difficult to explain. I basically have a Word document containing addresses in this format:

John Smith
100 Street St
Streetly
W1 1AA

And there's about 100 such addresses inside a bunch of tables, with one address per cell. I need to move these over to an Excel sheet, so that each address is either inside a single cell, like this:

| John Smith, 100 Street St, Streetly, W1 1AA |

Or, each line of the address is on consecutive columns (this way would save me quite a bit of time later on, actually), like this:

| John Smith | 100 Street St | Streetly | W1 1AA |

However, just copying and pasting them over doesn't do the trick because the addresses will only paste into multiple cells (I presume because there's line breaks in the formatting), and all in the same column.

Anyone know of a way to make them paste to a single cell each, or even for each line to paste to consecutive rows? I suspect there's not and I'm going to have to copy/paste each line of all 100 of these things, but hopefully one of you Excel nerds *ahem* experts knows of a shortcut

 

[Columbo]

If there is a comma at each required cell split then you could save it as a text a file then import it into excel comma delimited.

 

[kissenger]

No unfortunately it's just each line, then a line break, then the next line.

 

[Grrrrr]

This looks like it will achieve what you need

https://social.msdn.microsoft.com/F...-excel-2010-suite-and-vba-macro?forum=worddev

I read through the code and one thing you might want to change is

Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)

I think the 'clean' function will get rid of the line breaks, so try

Cells(iRow, iCol) = .cell(iRow, iCol).Range.Text

 

[Grrrrr]

And if that gets each Word cell into eachExcel cell as expect you can then use MID and FIND and CHAR(10) to separate out each line into different columns. CHAR(10) is the character for line break.

So to extract the first line you could use:

=MID(A1,1,FIND(CHAR(10),A1,1))

To get the second and third lines will need different formulae. I'll let you figure that out