Zip Code Formatting Options in Excel


Microsoft Excel has a variety of methods for formatting zip codes, enough to be a bit confusing to the non-expert user (which covers just about most of us).  Fortunately,  CDXZipStream, our Excel add-in for analyzing address and zip code information, can handle both five-digit and nine-digit (+4) formats and can also automatically assume that a leading zero exists even when Excel drops it from a zip code (e.g. 08030 turns into 8030).  Nevertheless, for viewing and printing purposes, you will want to be able to control the appearance of zip code data using one of the following techniques:

1.Force the cell contents to be viewed as text using an apostrophe

In this case just simply enter an apostrophe before the cell contents, and this will force Excel to display the entry as text “as is” regardless of the cell formatting.  For instance, a cell with general or number formatting will display the entry 08030 as 8030, but by using the apostrophe like this (‘08030), the leading zero will be retained.   This is a good approach if you have a fairly short zip code list.

2. Use the zip code formats provided with Excel

Excel has built-in five and nine-digit formats that you can access from the Format Cells dialog box, under the Number tab.  Under the Special category, select the Zip Code or Zip Code + 4 format that works best for your data:



3. Make your own custom format

From the Format Cells dialog box shown above, under the Number tab, select the Custom category and under Type, input 00000 or 00000-0000 for a five or nine-digit code, respectively.  Leading zeroes will be retained.

4.Use the TEXT cell function

This requires the use of a formula in a nearby cell.  For example, if the first zip code in your list is in cell A1, type the following in cell B1:

= TEXT (A1, “00000”) 

This automatically converts the value to text, regardless of how the cell is formatted, and retains leading zeros for the first five digits of the code.  You can then paste the formula down the rest of your list to convert all the zip codes to this format.

Keep in mind that all the data returned from CDXZipStream are returned as text.   If you are using the LOOKUP cell function with this returned data, then the lookup values, such as zip codes, must also be in text format.  This applies to finding zips in a radius area, as illustrated in the video below:

The YouTube version of this video is here: Find Zip Codes in a Radius Using Excel.

In this case use options 1 and 4 above, which forces the zip codes to text independent of cell formatting; and for very long lists, use option 4. 

Add comment