|
Why
Can't Excel Find My Zip Code?
One
of the most common support questions we get
involves difficulties matching up zip codes in a
spreadsheet analysis. A typical situation is
a radius search where an Excel function named
VLookup is used to compare a list of addresses to
a set of nearby zip codes within a certain
distance. Users often report that all the
formulas return #NA indicating that no matches
were found. Another concern is not being
able to find the matching zip code from a set of
addresses obtained from a database or copied from
the internet. So what's causing this and
what can you do about it?
In
most cases, the problem is due to extra data,
often hidden, appearing with the imported address
or zip code data. Importing data from other
sources often brings along non-printing
characters. This causes a zip code such as
"07869" to instead be entered as "07869 " (with a
blank space at the end) which will not compare
correctly. You may not be able to see this
problem simply by looking at your
spreadsheet, but if you inspect the data in
the Excel formula bar to edit it you will see the
extra characters represented as blank
spaces.
For
a radius search you need to make sure that you are
comparing similar data. The CDXRadius
function returns a list of 5 digit zip codes in
text format and need to be matched up with
identical items in your address list. If the
address list contains Zip + 4 or other numeric
data, problems will occur. And if zip
code data is input into a cell formatted for
numbers, all leading zeroes will be dropped
causing further difficulties matching up
results.
In
addition to making sure that you have your data
formatted as text you can also use the Excel LEFT
function to return only the first five characters
of your zip code column. We've written a
support article on doing this in a radius search
which can be found here.
The CDXZipCode function can also return a properly
formatted zip code from your data. This will
automatically trim Zip + 4 entries to five
characters and restore any dropped leading
zeroes.
If
your data is text and doesn't involve zip codes,
other functions Excel functions can be used.
Suppose you have a valid city, state combination
but it has extra blanks or non-printing
characters. Simply enter the formula
"=CLEAN(TRIM(D2))" in an adjacent column, where D2
in this case is the cell containing the data you
need to modify.
Using
the above techniques with Excel and CDXZipStream
should in almost all instances solve your problems
with matching data in a zip code
analysis. | 
