How to convert multiple lines in word to single cell in excel?

This might work ..

Assuming you have copy pasted in col A
the samples below, in A1 down

and the address structure is nicely
in groups of 4 lines + 1 blank line in-between

Christian PaPa
10280 AAAAAAA
BBBBB, Quebec J6M 5T6
Canada

Bruce MaMa
575, BBBBBB,
Provence d'Azur 06700
France
etc

Put in B1: =OFFSET($A$1,ROW()*5-5+COLUMN()-2,)
Copy B1 across 4 cols to E1, then copy down until zeros appear

This will re-arrange the data in col A into 4 separate columns, in cols B to
E

If you want to start the above in a row other than row1,
and in a column other than col B,
say in C4 instead of B1, use:

=OFFSET($A$1,ROW(1:1)*5-5+COLUMN(B:B)-2,)

Just copy the formula across 4 cols,
then copy down until zeros appear, as before

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
---
"leasaac " wrote in message
...
Hi,

I need help in converting multiple address lines in word to single
columns in excel. I hope someone have a solution for it.

My word file is a labels format where the addresses are seperated in
2X8 columns(A4 size paper).

For example:
(Word)
Christian PaPa
10280 AAAAAAA
BBBBB, Quebec J6M 5T6
Canada

Bruce MaMa
575, BBBBBB,
Provence d'Azur 06700
France

I want to convert them to:
(Excel)
Christian PaPa 10280 AAAAAAA BBBBB, Quebec J6M 5T6 Canada
Bruce MaMa 575, BBBBBB, Provence d'Azur 06700 France

Right now I am doing copying and pasting of the address single handedly
and it is very time consuming.

Anyone can help me this or is there a way to simplify the process?

Any help is deeply appreciated.


---
Message posted from http://www.ExcelForum.com/