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

  • Thread starter Thread starter leasaac
  • Start date Start date
L

leasaac

Hi,

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

My word file is a labels format where the addresses are seperated i
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 handedl
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
 
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
Click to expand...
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
 
I had a similar requirement of converting multiple lines in word to separate columns in excel. The solution worked for me. Its simple and elegant!

Thanks!
 
Back
Top