When entering ZIP Code data into an Excel spreadsheet,
you may have noticed that Excel will drop leading zeroes, such that
the ZIP Code "07869" will appear as "7869".
This often occurs in a new spreadsheet where the default cell
formatting is "General" number formatting, so Excel treats
the ZIP Code as a number instead of a text entry.
In most CDXZipStream functions, such as CDXDistance
and CDXRadius, the leading zero is automatically assumed during
calculations involving any four-digit ZIP Code entries. This is
not the case for functions using Bing Maps or MapPoint, since postal
code formatting in various countries do include legitimate four-digit
codes. For example, when using CDXRouteBing to calculate the
driving distance between two ZIP Codes with dropped leading zeroes,
the following error will occur:
Reformatting the data using Excel's ZIP Code format
(under the "Special" category) will show the leading zeroes
again in the cells, but will not change the CDXRouteBing error.
Here are some ways to correct this problem:
1. Use the TEXT function. If the
ZIP Codes are already in your spreadsheet, use the text function in
another column to convert the data to a 5-digit text entry, using the
following formula: = TEXT(A1, "00000") for a ZIP Code
in cell A1. Copy this formula down the column to convert all
the data. Then copy the results as values into the original
column of data.
2. Format the destinaton as text.
Before entering ZIP Codes into the spreadsheet, format the
destination column as text. Then copy and paste the data as
values (so as not to change the formatting of the destination).
If the original set of data were text entries showing the
leading zeroes, they should be retained after the paste operation.
3. Use a custom function. You can
easily create your own custom function to restore ZIP Code leading
zeroes.
In Excel, use ALT-F11 to open the Visual Basic for
Applications (VBA) editor. From the Insert menu of the editor,
select "Module". In the new module window that opens,
copy and paste the following:
Function ZIPFORMAT (zipcode)
ZIPFORMAT = Left(Format((zipcode),
"00000"), 5)
From within your worksheet, use the function like
this: = ZIPFORMAT(A1) to reformat the ZIP Code in cell A1.
If your ZIP Codes originate from a database, they may
contain non-printing characters that can also cause problems.
To remove non-printing characters, use the CLEAN function.
In this case the VBA formula is as follows:
ZIPFORMAT = Left(Format(Application.WorksheetFunction.Clean(zipcode),
"00000"), 5)
