View Full Version : 9 digit zip code imports as 0, not 9 digits
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
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:
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
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
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.