Yesterday I received a letter addressed with a zip code that looked like this ‘02906. So what is the big deal you ask? Well the tiny little apostrophe tells me two things. Most likely this person used mail merge to print all the addresses on labels. The second thing is that they do not know about a nice formatting feature built into Excel. The problem with spreadsheet programs is that they do not understand the difference between a basic number and a zip code. So when you type in a zip it drops the zero. Confused? Let’s say you are payed $0100.00. We normally drop a zero on the left hand of a number that is not preceded by a number meaning we change $0100.00. to $100.00. Many people see this and add the apostrophe to stop it from changing. This solves the problem to a degree but every zip now has an extra symbol in it. The easy way to fix this is to highlight the data or a whole column of zip codes and then click on the format menu and choose cells. From the list of categories you should choose special. Once you do you will see zip code and zip code plus four. I usually choose just zip code and then hit OK. This will add in all the missing zeros. You can do this before or after you put them in. So if you are planning on keeping track of addresses in Excel give this one a try.
Wednesday, November 28, 2007
“We relax at the ranch, which if not heaven itself, probably has the same zip code.” Ronald Reagan
Yesterday I received a letter addressed with a zip code that looked like this ‘02906. So what is the big deal you ask? Well the tiny little apostrophe tells me two things. Most likely this person used mail merge to print all the addresses on labels. The second thing is that they do not know about a nice formatting feature built into Excel. The problem with spreadsheet programs is that they do not understand the difference between a basic number and a zip code. So when you type in a zip it drops the zero. Confused? Let’s say you are payed $0100.00. We normally drop a zero on the left hand of a number that is not preceded by a number meaning we change $0100.00. to $100.00. Many people see this and add the apostrophe to stop it from changing. This solves the problem to a degree but every zip now has an extra symbol in it. The easy way to fix this is to highlight the data or a whole column of zip codes and then click on the format menu and choose cells. From the list of categories you should choose special. Once you do you will see zip code and zip code plus four. I usually choose just zip code and then hit OK. This will add in all the missing zeros. You can do this before or after you put them in. So if you are planning on keeping track of addresses in Excel give this one a try.
Subscribe to:
Post Comments (Atom)
3 comments:
Hi Shane, I tried doing as you suggested in Excel and it fixed the zero zip code issue in my spreadsheet. However, when I did a mail merge using the spread sheet in Word, the zeros were gone again. Do you know how to correct this?
It seems 2003 has a conversion. If you check this PDF out and go down to page 17 at the bottom it will give you the instructions. Give that a try.
http://www.itg.ias.edu/train/materials/Word_2003.pdf
The best way to handle this situation IS to use the apostrophe. It converts the zip code to text. In this way, you will NEVER have a problem with the mail merge.
The better way to handle this is to format the entire column as TEXT, not use the Special category. If you use the Special option, the cell will display properly, but if you look in the formula bar, you will see that the leading zero is STILL dropped. And, this may cause problems with exports and mail merges.
Post a Comment