Results 1 to 9 of 9

Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

This is a discussion on Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero? within the Excel Questions forums, part of the Question Forums category; I have 5 digit and 5+4 = 9 digit zip codes in Excel. Some start with 0 such as 01122. ...

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    30

    Default Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    I have 5 digit and 5+4 = 9 digit zip codes in Excel. Some start with 0 such as 01122. Using the mail merge wizard, the zip codes that begin with zero are getting dropped, which is not what I want. I dont want it to convert a zip from 01122 to 1122, because a zip code should include the leading zero, not drop it.

    Ive tried in MS Excel formatting the fields to custom zip of 5 or 9 digits and they still get the leading zeros dropped after the mail merge conversion. What should I be doing differently?

  2. #2
    New Member
    Join Date
    Nov 2012
    Location
    Maryland, USA
    Posts
    16

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    Unfortunately I don't see a Mail Merge on my end, but you could try the Custom scroll down to the bottom and use the 00000-0000 format.

  3. #3
    Board Regular
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    815

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    The following Word field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named ‘ZipCode’ is assumed.

    {QUOTE
    {SET Zip {MERGEFIELD ZipCode}}
    {IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
    "{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

    Note: The field brace pairs (ie '{ }') for the above example are created in the body of the document via Ctrl-F9 (Cmd-F9 on a Mac) - you can't simply type them or copy & paste them from this message.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,842

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    In Word you can use switches to format the fields.

    For example if the field for zip codes was called ZipCode.

    {MERGEFIELD ZipCode \#"00000-0000"}

    You can add the switch by right clicking the field and selecting Toggle field codes.
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    815

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    Quote Originally Posted by Norie View Post
    In Word you can use switches to format the fields.

    For example if the field for zip codes was called ZipCode.

    {MERGEFIELD ZipCode \#"00000-0000"}

    You can add the switch by right clicking the field and selecting Toggle field codes.
    That gives the wrong result for 5-digit Zip codes (eg a Zip code of 01234 would be rendered 00000-1234)
    Cheers
    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Member
    Join Date
    Nov 2012
    Posts
    48

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    You can try using =TEXT(A1,"00000") for those zipcodes with dropped zeroes.

  7. #7
    Board Regular
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    815

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    Quote Originally Posted by saelageonzon View Post
    You can try using =TEXT(A1,"00000") for those zipcodes with dropped zeroes.
    That won't help, as the issue is with Word, not Excel.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Member
    Join Date
    Apr 2010
    Posts
    30

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    Quote Originally Posted by fletcherj23 View Post
    Unfortunately I don't see a Mail Merge on my end, but you could try the Custom scroll down to the bottom and use the 00000-0000 format.
    This doesn't work, because after trying both CUSTOM and SPECIAL, both convert all 5 digit zips within Excel from 12345 to 00001-2345, even before trying to convert them into MS Word. Right now, they are formatted as SPECIAL - ZIP CODE.

  9. #9
    New Member
    Join Date
    Apr 2010
    Posts
    30

    Default Re: Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    I should hit CTRL F9 and insert all 4 of your lines? I tried that and couldnt get it to work.
    1 It still incorrectly changed as an example, 01234 to 1234 as the zip
    2 It listed all those formatting marks

    Here is the result as shown on my letter:
    100 Danbury Road
    Ridgefield CT
    {SET Zip {MERGEFIELD 6877}}
    {IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
    "{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}


    Quote Originally Posted by Macropod View Post
    The following Word field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named ‘ZipCode’ is assumed.

    {QUOTE
    {SET Zip {MERGEFIELD ZipCode}}
    {IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
    "{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

    Note: The field brace pairs (ie '{ }') for the above example are created in the body of the document via Ctrl-F9 (Cmd-F9 on a Mac) - you can't simply type them or copy & paste them from this message.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com