{"id":106,"date":"2016-10-11T09:00:10","date_gmt":"2016-10-11T13:00:10","guid":{"rendered":"http:\/\/figartconsulting.com\/?p=106"},"modified":"2016-10-10T16:57:18","modified_gmt":"2016-10-10T20:57:18","slug":"zero-and-zip-codes-in-excel","status":"publish","type":"post","link":"https:\/\/figartconsulting.com\/wordpress\/2016\/10\/11\/zero-and-zip-codes-in-excel\/","title":{"rendered":"Zero and Zip Codes in Excel"},"content":{"rendered":"

Once upon a time, there was a completely random and fictional person<\/em> 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<\/em> person truly was pretty good with MS Office, but had gotten a bit cocky and wasn’t quite as good as she thought.<\/p>\n

This (honest!) completely random and fictional person<\/em> had a client in Northern New England. The location will become important to the story later.<\/p>\n

The spreadsheet had a list of ten thousand addresses. But the completely random\u00a0and fictional\u00a0<\/em>person<\/em> was undaunted. After all, the source spreadsheet looked wonderful \u2013 as well, laid-out and sensible a table as one could wish for when creating an address list:<\/p>\n

\"\"<\/p>\n

Beautiful, right? It was going to be an easy mail merge.<\/p>\n

(Y’all’ve guessed it wasn’t, I am sure.)<\/p>\n

So the completely random\u00a0and fictional\u00a0<\/em>person <\/em>set up the mail merge and did something pretty stupid regarding printing out ten thousand labels. This completely random\u00a0<\/em>and fictional\u00a0<\/em>person <\/em>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.<\/p>\n

At the printer, the completely random\u00a0<\/em>and fictional person <\/em>found ten thousand<\/em> labels with addresses like this:<\/p>\n

\"\"<\/p>\n

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?<\/p>\n

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.<\/p>\n

Take a look at the actual contents of the zip code cell for Boris Badenov:<\/p>\n

\"\"<\/p>\n

Even though the cell is set to display<\/em> as a five-digit ZIP code, the value<\/strong> in cell F2 is Three Thousand Seven Hundred Sixty-six, not Zero-Three-Seven-Six-Six.<\/p>\n

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<\/em> and will strip out the numerically meaningless preceding zero.<\/p>\n

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

Unless\u2026<\/p>\n

Instead of formatting the cells as numbers and a ZIP code, you format those cells as text<\/em>. Then you can keep the preceding zero just fine.<\/p>\n

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.<\/p>\n

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!)<\/p>\n

\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … <\/p>\n

Continue reading “Zero and Zip Codes in Excel”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":102,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8,5],"tags":[],"class_list":["post-106","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-nifty-tips"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/figartconsulting.com\/wordpress\/wp-content\/uploads\/2016\/10\/101016_1758_ZeroandZipC1.png?fit=575%2C224&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pLg3p-1I","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/posts\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/comments?post=106"}],"version-history":[{"count":4,"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":111,"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/posts\/106\/revisions\/111"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/media\/102"}],"wp:attachment":[{"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/media?parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/categories?post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/figartconsulting.com\/wordpress\/wp-json\/wp\/v2\/tags?post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}