Zero and Zip Codes in Excel

Once upon a time, there was a completely random and fictional person who had to convert a mailing list from an Excel spreadsheet to a series of mailing labels via Mail Merge in Word. This completely random and fictional person truly was pretty good with MS Office, but had gotten a bit cocky and wasn’t quite as good as she thought.

This (honest!) completely random and fictional person had a client in Northern New England. The location will become important to the story later.

The spreadsheet had a list of ten thousand addresses. But the completely random and fictional person was undaunted. After all, the source spreadsheet looked wonderful – as well, laid-out and sensible a table as one could wish for when creating an address list:

Beautiful, right? It was going to be an easy mail merge.

(Y’all’ve guessed it wasn’t, I am sure.)

So the completely random and fictional person set up the mail merge and did something pretty stupid regarding printing out ten thousand labels. This completely random and fictional person neglected to do a print preview, set that bad boy to print and went for a cup of coffee and chat with the client before going back to the printer.

At the printer, the completely random and fictional person found ten thousand labels with addresses like this:

Can you spot the error? Check out those ZIP codes. ZIP codes in the US have a minimum of five characters. So why in the world didn’t the beautiful (and correct!) zip codes in the spreadsheet translate to the mail merge?

When you do a mail merge, what Word actually does is take the characters in the cell (or the results of a formula) from the spreadsheet and copies them to the proper field in the document.

Take a look at the actual contents of the zip code cell for Boris Badenov:

Even though the cell is set to display as a five-digit ZIP code, the value in cell F2 is Three Thousand Seven Hundred Sixty-six, not Zero-Three-Seven-Six-Six.

So, what’s the numeric difference between those two numbers? None at all. It is only as a string of characters, the actual Arabic numeric symbols, that this becomes important. When you enter a string of Arabic numeric characters into a cell in Excel, it immediately formats that string as a number and will strip out the numerically meaningless preceding zero.

When you format cells as a ZIP code, it will take that string of four characters, and add a zero for you to see in Excel, but it does not store that zero as an actual character.

Unless…

Instead of formatting the cells as numbers and a ZIP code, you format those cells as text. Then you can keep the preceding zero just fine.

The problem comes in when you hand someone an assignment to enter a bunch of addresses (including some New England addresses) into a spreadsheet. Unless the person doing data entry is thinking regarding data transfer to other applications, they’re not going to bother to think about doing anything other than just formatting the zip code as a zip code. They won’t give it another second’s thought.

If you’re going to be using Excel to store data that has a preceding zero that you will be using in mail merges or transferring to other programs, be sure to select that column of information, and format it as Text. (I don’t recommend the trick of the preceding apostrophe, i.e. ‘03766. While it does, indeed, cause Excel to format the data as text and only display the numbers, it plays hob with data transfers like mail merges!)

One Reply to “Zero and Zip Codes in Excel”

Leave a Reply to Ted Jerome Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.