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

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.

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.
--
Kevin Backmann


"Omega" wrote:

> 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.
>

Kevin,

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

Omega

"Kevin B" wrote:

> 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.
> --
> Kevin Backmann
>
>
> "Omega" wrote:
>
> > 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.
> >

"Omega" <[email protected]> wrote in message
news:[email protected]...
>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.
--
David Biddulph

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.

"David Biddulph" wrote:

> "Omega" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> --
> David Biddulph
>
>
>

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/as...164951033.aspx

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

http://sbarnhill.mvps.org/WordFAQs/C...ngWord2002.htm

about half way down the page.

Omega wrote:
>
> 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.

--

Dave Peterson

"Omega" <[email protected]> wrote in message
news:[email protected]...

> "David Biddulph" wrote:
>
>> "Omega" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.

> 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).
--
David Biddulph

Dave,

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

Omega

"Dave Peterson" wrote:

> 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/as...164951033.aspx
>
> And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill
> has instructions here:
>
> http://sbarnhill.mvps.org/WordFAQs/C...ngWord2002.htm
>
> about half way down the page.
>
> Omega wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>