Leading Zeroes Missing from Zip Codes in a Mail Merge: Another Option

The best solution to this problem is to change an option in Word that sets up a DDE connection with Excel, and displays the zip code the way it is formatted in Excel, with all leading zeroes. (See Mail Merge Problem: Leading Zeroes Missing from Zip Codes for instructions.)

However, for a variety of reasons (such as a filtered spreadsheet or a conflict with a printer driver), this doesn’t always work. Another option is to edit the field codes in Word to make sure all the leading zeroes appear. Here’s how:

  1. In the Word document, click into the field with the zip code, and press SHIFT + F9.
  2. Word will display the field code. It will look like this (with Zip_Code being the name of the field):
    {MERGEFIELD Zip_Code}
  3. Click into the field and change it to the following:
    {MERGEFIELD \# "00000" Zip_Code}

    Format explanation: I picked five zeroes for the zip code format because a zero in a number format forces Excel to put a number in that position, even if the number is blank or zero. (The default number format uses # signs, which to Excel means, “Don’t put a number in this position if the number is blank or zero.”)

  4. Click back into the field and press SHIFT + F9 to hide the field code and display the text.
  5. If you do not see the leading zeroes, click into the field and press F9 to update the field.

Comments

Leading Zeroes Missing from Zip Codes in a Mail Merge: Another Option — 32 Comments

  1. Pingback: Mail Merge Problem: Leading Zeroes Missing from Zip Codes | Notes from the Help Desk

  2. Would yu please be more specific as to which word document one clicks into the filed code in, and how do you click into a field code? This is not an apparent choice in my merged, zero-less document!

  3. You want to click into the field in your main document, not in the merged document. Here are the steps:

    1. After you insert fields (First Name, Last Name, etc.) into the main document, click into the middle of the Zip Code field. It will probably look something like this: <>

    2. While you’re clicked into the middle of the Zip Code field, press SHIFT + F9. You see the field as it is pictured in the article above, and you will able to add the zip code format.

  4. I use Excel,Office 36, and this method worked perfect. The instructions for confirming the file format on open was not an option for me. Thank you for this alternate way of retaining leading zeros.

  5. If you save your excel file as CSV(comma delimited) and then use this file (CSV Version) as database in Microsoft Word Mail Merge when creating labels it will keep all the zeroes from zip codes.

  6. A far easier fix is to avoid use of the address block and insert the address fields individually. Just prior to the zipcode field, type a zero with no space, then insert the zipcode field. So long as all your address are to leading zero zipcodes (mine are…I realize it may not be so easy for some), it works like a charm. Can’t get this “other option” to work for Office 365.

  7. Thank you for your helpful tip! It worked perfectly. I will add this as a Best Practice for other members of my team. Thank you for making me look like an expert!

  8. Yes I had the same problem. Office 365 this fix does not work. Zip Code field just disappears. Strange how I never had this problem on a Mac. I thought Word and Excel would be more compatible on a PC, not less.

  9. this works for data bases with home addresses or business addresses only but it doesn’t seem to work when you have a mix of addresses (both home and businesses)?

    • Not sure what you mean. It will work for any zipcode. Are you saying that sometimes you have 2 addresses and sometimes you have 1? If so, you need to program around that–test for the second address.

  10. The MERGEFIELD formatting approach works fine with zip codes but Canadian postal codes appear as “00000”. Any suggestions other than separating the mail merges by country and removing the formatting?

    Thanks
    Dave

    • I understand that Canadian Postal Codes are alphanumeric so the formatting changes should not be necessary. You could use an If statement to say that if the code starts with a letter, it is Canadian and requires no formatting and if it starts with a number, it is US and needs the formatting. The 00000 for Canadian codes is probably because the codes are not numeric. The formula, which you would have to enter as an IF-THEN-ELSE formula and then add the mergefields directly is this: (Press ALT-F9 to see the codes)

      {IF { MERGEFIELD Zip_Code} < 0 {MERGEFIELD Zip_Code} { MERGEGIELD Zip_Code /#"00000"}} The Zip_code value will be less than 0 if it contains text.

  11. My mergefield looks like as follows: { MERGEFIELD CityStateZip }

    I tried editing to look like: { MERGEFIELD CityState\# “00000” Zip } and it updated the zip code correctly but it removed the City and State any suggestions?

  12. Another solution that worked for me was converting the zip-code field format to “Text” (not number and not general) and for any zips with leading zeros to preface with a ‘ (single quote). The merge worked for leading zeros and for 9 digit zips xxxxx-xxxx

Leave a Reply

Your email address will not be published. Required fields are marked *