Converting Addresses from Word

[Guest]

I have a Word document that was given to me with addresses. In word the
addresses are in two columns with the Name, Address and City/State/Zip on
seperate rows. I would like to copy or import this into excel to use as a
mail merge. Can this be done without having to mannually manipulate the data
too much?

 

[Gord Dibben]

Assuming your data is is in columns A and B like such......

name1 name4
addy1 addy4
city/state/zip1 city/state/zip4
name2 name5
addy2 addy5
city/state/zip2 city/state/zip5
name3 name6
addy3 addy6
city/state/zip3 city/state/zip6


Cut column B and paste below data in column A.

Enter this formula in B1

=INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:B)-1)

Copy across to D1.

Select B11 and copy down until you get zeros showing up.

Copy columns B and paste special(in place)>values>ok>esc

Delete column A


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Afterthought<g>

If you get your data in three columns, you might want to use Data>Text to
Columns to break city/state/zip into three columns also.

Easier to work with in your mailmerge.


Gord