Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
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) –  Pretzel Jun 10 '10 at 12:27
    
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. –  Lunatik Jun 10 '10 at 13:56
    
I tried that already. (unless I did it wrong somehow.) Converting to Text just brought everything back to the values without the leading zeros. –  Pretzel Jun 11 '10 at 16:37
    
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 :) –  Lunatik Jun 11 '10 at 19:21

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

share|improve this answer
up vote 0 down vote accepted

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.

share|improve this answer
    
The Alt F9 {MERGEFIELD "Shipping ZIP" \#"00000"} works. If you have all Zip+4 codes use Alt F9 {MERGEFIELD "Shipping ZIP" \#"00000-0000"} –  user163503 Oct 6 '12 at 20:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.