Check out some similar questions!

zix · Oct 27, 2006, 03:15 PM

I am using MS Word and MS Excel 2003. From MS Word, I have done a MAIL MERGE, and some of the zip codes are 5 digit, while others are 9 digit, as in 12345-6789. After doing the mail merge, and previewing the letters, the 9 digit zip codes get imported as 0. Certainly the MS Word software should be able to recognize a 9 digit zip code as a zip code? The 5 digit zip codes import properly as 5 digit zip codes.

How can I make the 9 digit zip codes display properly after doing the mail merge into MS Word? What am I doing wrong?

zix · Oct 27, 2006, 04:29 PM

I found A solution. I went back into the spreadsheet and changed the ZIP CODE fields format to TEXT and then re-merged. Apparently if a 9 digit zip is not in a text format, it converts the 9 digit zip code to the number 0.

Dale F Wiley · Oct 31, 2006, 02:13 PM

Hi Zix,

I tried to duplicate what is going on with you and no success.
My Mail Merge worked just fine with 9 digits, well 10 counting the dash.
Try formatting the Zip Code column as General or Text in Excel.
There is always the possibility of a corrupted document or a corrupted Normal.dot or worse.
If this doesn't work try a new document and see if the same thing happens.

Good Luck.
Dale :cool:

ScottGem · Oct 31, 2006, 02:17 PM

To Dale, The dash means that the cell was formatted as text hence the reason you didn't have the problem. I suspect, that in Zix's case the dash causes Excel to consider it a subtraction not text.

Dale F Wiley · Oct 31, 2006, 03:00 PM

Originally Posted by ScottGem

To Dale, The dash means that the cell was formatted as text hence the reason you didn't have the problem. I suspect, that in Zix's case the dash causes Excel to consider it a subtraction not text.

Good point, well done. :cool:

zix · Nov 6, 2006, 11:32 PM

One other problem I had was when it converted zip codes beginning with 0 such as 06543, it dropped the zero and changed it to a 4 digit zip of 6543!

I had to add all the preceding zeros back in to the 4 digit zip codes!

colbtech · Nov 7, 2006, 01:08 AM

Select the Zip Code cells and then click on Format, Cells, Number, Custom. In the box below Type, enter 9 zeroes. Click OK. All the cells will be formatted with leading zeroes.

ScottGem · Nov 7, 2006, 07:04 AM

Originally Posted by zix

One other problem I had was when it converted zip codes beginning with 0 such as 06543, it dropped the zero and changed it to a 4 digit zip of 6543!

I had to add all the preceding zeros back in to the 4 digit zip codes!

This is why ZipCodes should be text data types, not numbers. In a database, only numbers that are using in calculations, should be formatted as a number datatype.

zix · Nov 16, 2006, 07:16 AM

Originally Posted by colbtech

Select the Zip Code cells and then click on Format, Cells, Number, Custom. In the box below Type, enter 9 zeroes. Click OK. All the cells will be formatted with leading zeroes.

I suppose this would have worked, but not for me, since my spreadsheet has both 5 digit zip codes AND 9 digit zip codes. When I tried it, it turned my zips like 12345 into 000012345.