MS Word 2007 Mail Merge fails on ZIP codes with leading Zeros (eg. 01234)

Question

I have an Excel Spreadsheet with a ZIP code column. For some dumb reason the original spreadsheet I got had all the zip codes stored as numbers, so a ZIP code like 01234 was stored as 1234. Easy to fix with "Format Column" as "Special => ZIP Code". All values like 1234, show up as 01234. Great!

When I import it into Word via Mail Merge (to print address labels), the ZIP codes on all the addresses starting with a leading zero (like 01234) revert to their old form (1234).

How do I fix this?

Solution 1

I managed to figure out how to fix it in Word, though. When in the midst of Mail Merge, do the following:

• Press ALT+F9

This should switch the Mail Merge over into some sort of formatting edit mode. (I don't know what it's called.)

• Edit your ZIP code field

Mine looked like this before ALT+F9: «Shipping_ZIP»

And looked like this after ALT+F9: {MERGEFIELD "Shipping ZIP"}

Change it to look like this: {MERGEFIELD "Shipping ZIP" \#"00000"}

(You're inserting this: \#00000 which appears to add padding of 5 zeros.)

• When you're done, push ALT+F9 again (to get out of the edit mode)

Unfortunately, this breaks the system if you have ZIP+4 codes or non-US postal codes, but it worked well enough for me since virtually all the zip codes I had to print were 5 digit ones.

Solution 2

The simplest way of achieving this would be to set the number format of the ZIP codes in Excel to Text. This way they'll import with leading zeroes intact (I've just tested to confirm).

The only real downside to this that I can see is that, depending on your Excel configuration, you may the little green arrows telling you you have numbers formatted as text on that column. Not a biggie, but something to be aware of.

Solution 3

I have never had this problem before, and I tried several proposed solutions. The Alt F9 is the only one that worked

Comments

[Pretzel]

This doesn't work, because as soon as you set the column to "Text" in Excel, the values revert from (eg. 01234) back to (eg. 1234)

[Lunatik]

Well yes, if the leading zeroes have already been stripped then this will not know to add them back. You'd need to format the column as 00000 first, then change to text.

[Pretzel]

I tried that already. (unless I did it wrong somehow.) Converting to Text just brought everything back to the values without the leading zeros.

[Lunatik]

If you had already converted the column to 'Text' you would have to change the format to 'Number', specifically format '00000', then convert back to text in order to retain the leading zeroes. Sounds like you have sorted it already though :)

[Admin]

The Alt F9 {MERGEFIELD "Shipping ZIP" \#"00000"} works. If you have all Zip+4 codes use Alt F9 {MERGEFIELD "Shipping ZIP" \#"00000-0000"}