Fix Zip Code Zero in Excel Address Spreadsheet

Quickly Fix Missing Leading Zero in Your Envelope Addressing Template File

When ordering addressed envelopes, you are required to fill out a spreadsheet with your guests’ names and addresses. This spreadsheet allows the option for zip codes to be on their own line instead of next to the city and state. If you choose this option and have guests from the upper east coast (where zip codes begin with zeros) you may notice a problem – the beginning zeros disappear! 01752 is now 1752. What gives?

In this post, we explain why leading zeros disappear in Excel, why that’s a problem, and how to fix it (so your east coast friends can come too).

Why Leading Zip Code Zeros Disappear

Excel (or other database programs) are geared towards numbers and formulas. Like a calculator that turns 4.00 into 4 or 0.5 into .5, Excel turns 01752 into 1752. It assumes you are doing math and that those zeros are insignificant. It doesn’t know any better.

See below (We typed zeros in front of all of those zip codes. We swear.):

Excel spreadsheet with missing zip code zeros

Envelope proof with missing zip code zeros

Why it Matters

Your envelopes will be printed exactly as they appear on your spreadsheet. If you have a bunch of four-digit, missing-zero zip codes, you’ll end up with a bunch of four-digit, missing-zero envelopes.

See right. Not a good look and not good for the post man.

Below, the three-step fix. It’s easy.

1. Click to Highlight Zip Code Column

Click column to highlight and format column in Excel

Left click the top of the zip code column (on the letter) to highlight the entire column.

2. Right Click, Select Format Cells


With the column highlighted, right click to bring up an options menu.

Select Format Cells as shown left.

3. Click Special, Zip Code

When the Format Cells menu pops up, select Special then Zip Code (or Zip Code + 4 if you want the extra numbers).

Click OK then check it out – your zeros have returned and will print on your envelopes as well.

Excel formatting cell menu Envelope proof with zero in front of zipcode

Save as a .csv Just ONCE

Our envelope addressing proofing system requires your addressing spreadsheet be saved as a .csv file.

After you format your cells to include leading zeros in zip codes, save the file just once. Excel often asks you to save it multiple times, but saving twice will clear the formatting and your zeros will disappear again.

Comments are closed.