How do I format Zip codes to print properly in a Mail merge?

[Guest]

I have created an Excel database containing addresses (more specifically zip
codes). I then have attempted to mail merge them into Word and have been
having a heck of a time getting the zip codes to print properly. Each time I
attempt to print the labels (ie., zip code 08080) the first zero of the zip
code will not not print for some reason. I have gone back and tried to
change the format of the cell that holds the zip code and still it does not
work. I have changed the format from Zip to custom (00000) and still no
success. Any suggestions from others who have had the same problem and found
a remedy would be greatly appreciated.

 

[Guest]

Formatting a numeric value only changes the appearance of the number, but not
its value. So, even though you can see leading zeroes in you spreadsheet
they are not part of the data.

You can insert a helper column to the right of the zip code column and then
use this formula to convert you zips to text and pad with a leading zero if
necessary:

=IF(LEN(A1)=4,"0"&TEXT(A1,"#"),TEXT(A1,"#"))

Substituting the first zip code cell for A1 in the formula above and copy
down the helper column to the last row having zip codes. Then copy the
entire column containing your coversion formula and then move to the first
cell containing you original zip code values. Click EDIT in the menu, select
PASTE SPECIAL, click the VALUES option button and click OK. Delete the
helper column and you're back in business.

 

[Guest]

Kevin,

I will try this and let you know how it works. Thanks alot!!

Omega

 

[David Biddulph]

I have created an Excel database containing addresses (more specifically
zip
codes). I then have attempted to mail merge them into Word and have been
having a heck of a time getting the zip codes to print properly. Each
time I
attempt to print the labels (ie., zip code 08080) the first zero of the
zip
code will not not print for some reason. I have gone back and tried to
change the format of the cell that holds the zip code and still it does
not
work. I have changed the format from Zip to custom (00000) and still no
success. Any suggestions from others who have had the same problem and
found
a remedy would be greatly appreciated.

Apparently the content of your cell is the number 8080, not the text string
08080. Changing the format as you've done doesn't alter the content of the
cell, but if the cell is treated as a number it won't store the leading
zeroes.

You need to turn it into Text (not a number) & concatenate the leading
zeroes.

Try =REPT("0",5-LEN(A1))&A1

If you were starting from square one you could just format the cells as text
*before* you entered the data.

 

[Guest]

David,

I am not starting from Zero. I have the zips already in the worksheet, but
can not print them properly. I'm sorry to be such a novice at this, but I'm
not sure how to formulate the entire column to accept the formulas that you
have provided. Is there a way to insert the formalu that you have provided
so that it will simply adjust the entries that I have made without going down
the entire column with the new formula? Trying to get the hang of this so
that I don't have to start over from the beginning. Your help is greatly
appreciated.

 

[Dave Peterson]

Debra Dalgleish posted this:

There's an article on the Microsoft web site that might help you:

Answer Box: Numbers don't merge right in Word
http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx

And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill
has instructions here:

http://sbarnhill.mvps.org/WordFAQs/CustomizingWord2002.htm

about half way down the page.

 

[David Biddulph]

I am not starting from Zero. I have the zips already in the worksheet,
but
can not print them properly. I'm sorry to be such a novice at this, but
I'm
not sure how to formulate the entire column to accept the formulas that
you
have provided. Is there a way to insert the formalu that you have
provided
so that it will simply adjust the entries that I have made without going
down
the entire column with the new formula? Trying to get the hang of this so
that I don't have to start over from the beginning. Your help is greatly
appreciated.

Just copy the formula & paste it down the column, or drag the fill handle
[where the cursor turns to a + over the square in the bottom right-hand
corner of the cell] down the column (or just double-click on the fill
handle).

 

[Guest]

Dave,

That article did the trick. Thanks alot for your help and direction.

Omega