Word / Excel Help

Hello all

Im at work and someone has given me some bad data.

Its on word and looks like the fllowing

Ms a Sample
1 Sample Lane
Sample Town
Sample County
ME34 LALA
E-Mail: [email protected]

Ms a Sample
1 Sample house
Sample Lane
Sample Town
Sample County
ME34 LALA

E-Mail: [email protected]



I have 366 odd addresses where there the address lines range from 4 lines to 6 lines, gaps in data etc.

I need to get this into excell into a table with

name | Address line 1 | address line 2 | address line 3 | ETC

I have tried putting it inot excell deleting blanks then pasting into word, then doing Text to table convert but because the address lines are not spaced the fields come out in the incorrect collums rows etc Ideas>???? Thanks JL

You can copy each address block, and go to where you want to put the result, then go to Edit|Paste Special and select Transpose..

Repeat for each address block.

Another way using a formula would be...

Say your list is in Column A, starting at A2, and each group is separated by an empty row...

Then in B2, apply this formula: =IF($A1="",INDIRECT("A"&COLUMN(B1)),"") and copy it across 6 columns or more to get the data you need. Then copy them down your list.

The transposed address should appear next to each first line of each block.

Now select columns B to the last column and go to Edit|Copy.

Go to B1 and then Edit|Paste Special and select Values.

Now you can delete Column A which contains your originals.

Delete the Blank Rows between

See the attached.

Sorry about that... it was too early in the morning.....

Try: =IF($A1="",INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"") in B2, copied across and down