MS Word 2007 Mail Merge fails on ZIP codes with leading Zeros (eg. 01234)
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?
3 Answers
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.
4I have never had this problem before, and I tried several proposed solutions. The Alt F9 is the only one that worked
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.
1
