Converting Addresses from Word
 

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?

Converting Addresses from Word
 

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 B1:D1 and copy down until you get zeros showing up.

Copy columns B:D and paste special(in place)valuesokesc

Delete column A


Gord Dibben MS Excel MVP

On Thu, 19 Apr 2007 13:04:03 -0700, kspoms
wrote:

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?

Converting Addresses from Word
 

Afterthought<g

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

Easier to work with in your mailmerge.


Gord

On Thu, 19 Apr 2007 14:11:20 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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 B1:D1 and copy down until you get zeros showing up.

Copy columns B:D and paste special(in place)valuesokesc

Delete column A


Gord Dibben MS Excel MVP

On Thu, 19 Apr 2007 13:04:03 -0700, kspoms
wrote:

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?