Most of our customers who use our Microsoft Excel add-ins CDXZipStream and CDXStreamer for address and location analysis, are working with a lot of data in an Excel worksheet.  And if CDXZipStream or CDXStreamer are used to provide additional information for each point of data, it can be quite a task managing and analyzing all the results.  Here are some tips on how to approach data in a large Excel worksheet, particularly on how to find the data you need within a large data set.

First, if the data returned from the add-in is in formula form, get rid of the formulas by copying them to values.  Just select the area containing the formulas, and use the paste special option to paste values only.  The formulas will be replaced with their values; if a worksheet contains a lot of formulas, this will significantly speed up subsequent worksheet processing you’ll be doing later.  As an alternative, you can use the “Set Array to Values” option in the address verification function of CDXStreamer.   (You can also check out the navigation shortcuts described in our blog article 5 Tips all Excel User Should Know, which can help you move around and select large data sets.)

But how do you find just the data you need, in a very large worksheet?  Here are three ways available to do this efficiently and easily through Excel:

FILTERING:  This is Excel’s version of sorting on steroids.  The filter option allows you to look for items in a list based on lots of different criteria (that apply to both text and numeric values), and then hides the rows that don’t apply.  Select a cell within your data, and in Excel 2007 and up, from the Data tab on the ribbon, click the filter icon:

You’ll notice that drop-down arrows are now visible next to each column of data.  (You should make sure to label each column, so the first row of data is also part of the filtering process.)  Then click on the arrow in the column you’d like to filter, and based on the type of data present, you’ll be presented with text or numeric filter options.  You’ll also be able to do straight-on  sorting as well from here, but we’ll focus here on the filter functions.  For instance, if we just used CDXZipStream to calculate distances from a target destination, we can find all locations within a 50 mile radius by selecting the following options in the custom filter dialog:

A nice bonus is if you copy and paste the filtered data to another worksheet, the hidden rows are not included.  To remove the filter and show all rows of data, just click on the filter icon again.  For more information on filtering in Excel, please refer to the following Microsoft article: Filter Data in a Range or Table.

CONDITIONAL FORMATTING:  You can change the font, fill, and border colors and formatting depending upon the cell value.  From the Home tab, click on Conditional Formatting:

Using the example above, click on Highlight Cells Rules, and specify formatting for values less than 50:

For more information, check out the Microsoft article Add, change, find, or clear conditional formats.  

You can also combine conditional formatting with filtering, by using the “sort by color” option available in each column drop-down menu.  Please refer to the article Filter by cell formatting.

VLOOKUP/HLOOKUP:  Excel VLOOKUP or HLOOKUP are great worksheet functions that can link tables together when they have a common column or row of data.  For this example we’ll use VLOOKUP, which is applied to column-oriented data.  Let’s say we have a list of routes which have all been optimized using CDXZipStream; we would like to list all the destinations with their contact telephone numbers, but the optimization process has changed their order from their original list.  To find the telephone numbers in the original list associated with each destination, we create another column with the formula:

=VLOOKUP( LOOKUP_VALUE, TABLE_ARRAY,COL_INDEX,[RANGE_LOOKUP]) where

LOOKUP_VALUE  =  the destination address (in the optimized list)

TABLE_ARRAY = the range of cells that contains the destination addresses and telephone numbers in the original list.  The first column of this range should contain the destination addresses.

COL_INDEX = the column number of the TABLE_ARRAY that contains the telephone numbers

RANGE_LOOKUP = A logical value, where “FALSE” indicates an exact match is required

If we use a cell address for the first lookup value, we can copy this formula along all the optimized destinations to find their telephone numbers.  Make sure however, that the TABLE_ARRAY range is defined by either a named range, or by using an absolute cell range with dollar signs (e.g $A$1:$D$500).

For a list of optimized destinations in column A, we can search for the corresponding telephone number in the range $H$1:$K$200, where the destination addresses are in column H and the telephone numbers are in column K, with the formula:

=VLOOKUP(A1, $H$1:$K$200,2,FALSE)

Then just copy this all along the list to find all telephone numbers.

For more information, please refer to these articles about VLOOKUP and HLOOKUP.

We have often been asked if CDXZipStream can apportion a large number of destinations into individual routes, then optimize the order of stops along each of these routes.  Unfortunately the route optimization function of CDXZipStream, which relies on the use of Microsoft MapPoint, is not sophisticated enough to do this kind of calculation.  The vast number of permutations that could occur here requires a lot of computational muscle, and if there are rules (such as geographic constraints) that must be followed in apportioning the destinations, these must be programmed into the selection process as well.

But this is also a case where the human eye and a little brain power, with some help from Microsoft MapPoint, can compete pretty well with even the more sophisticated (and expensive) software.  The trick is to be able to quickly and easily visualize and manage the data, which is right up MapPoint’s alley.   So let’s get started.

Let’s say we have a list of 100 destinations, which we need to divide into 10 routes (of about 10 destinations each), followed by optimization of each route.     First we import all the destinations into MapPoint using the data import wizard available from the “Data” menu on the MapPoint toolbar:

From here you can then select the Excel file that contains the destination list; you can also select other file types here such as text or Access database files.  After selecting the desired file, you will also be asked to select the appropriate worksheet (assuming the file was an Excel workbook). Then click “Next”, and you’ll see a MapPoint dialog like this:

You can also choose to import data beyond just address information, which may be useful if this data is part of the decision-making process in apportioning routes.  (You can display all information associated with a destination by right-clicking on it later on the MapPoint map.)  Click on “Finish”, then select the pushpin map type in the next dialog:

Once the data is displayed as pushpins in MapPoint, you can use MapPoint drawing tools to apportion the data into routes.  The freeform tool was used to draw the area below:

You may also want to try the radius tool so you can specify the radius distance of the area.  Once an area has been drawn, right-click on the area boundary line and select “Import to Excel”.  You’ll immediately see all the points within the area in an Excel spreadsheet, ready to optimize with the CDXZipStream function CDXRouteMP.  You can also cut and paste these points into our Route Optimization template, available for free download from our links page.

This technique may not be appropriate in cases where there are many criteria for apportioning routes.  In the case above, we used logical boundaries to define the route area, such major highways and the river to the northwest.  (This is very easy for us humans to do, but can be pretty complicated for a computer.)   As mentioned previously, you can also import other data into the spreadsheet which can be used as criteria in apportioning.   This data can be displayed by right-clicking on a destination on the map, or even better, can be used to define the appearance of the destinations when selecting the map type, by selecting options such as color-coded pushpins or sized or shaded areas.  

Keep in mind that before optimizing each defined route, you may need to modify the beginning and end point destinations.  CDXZipStream assumes these points are fixed and only optimizes the order of the intermediate points.   In the majority of cases this assumption works, but you may also want to add one or more addresses that represent the true beginning and end points if they fall outside the area you just defined in MapPoint.

For more information on using CDXStreamer for route optimization, please watch the following tutorials:

Route Optimization in Excel

Route Optimization with One Click

CDXStreamer is our Microsoft Excel add-in that performs address verification and correction as well as data retrieval for ZIP+4, ZIP+4/TIGER and Canadian postal codes.  Here we will walk through the simple process of performing address verification and correction, which can virtually eliminate undeliverable addresses and significantly reduce the costs associated with incorrect address data.

Let’s say we have a long address list in Microsoft Excel, which could be addresses of customers, service call locations, delivery points, or other locations.  Here’s a portion of the list (in Column A):  

Select an empty cell next to your address list, and then use the cursor to click on the "Verify Address" icon on the CDXStreamer toolbar, as seen here in Excel 2010:

A dialog box will pop up:

Input the cell location of the first address in your list, in this case cell A2.  In this example the addresses are each listed within a single cell, but CDXStreamer can also handle addresses where the components are in separate cells.  In this situation, just select the “Multi-Line Address” option, and input the separate cell locations for the street, city, state and ZIP code. 

Now we choose the type of data to return to the worksheet.  Here we request the ZIP+4 code, also referred to as the 9digit_zip.  We also select the AutoCopy option so data will be returned for all the addresses in our list (so long as it doesn’t contain any empty rows).  Click OK, and the returned data in our worksheet looks like this:

In cases where an address match could not be found in the database, an error is returned.  All addresses that return N/A (an example in row 11 is highlighted in red) should be manually checked.

CDXZipStream will also correct addresses that contain limited errors, such as misspellings, incorrect or missing zip codes, cities, or states, and incorrect street suffixes and directionals.  Standard US Postal Service formatting, such as abbreviations and capitalization, will be also included.   To perform address correction, repeat the process described above, but specify that the entire address (full_address_out) be returned. This will also include the full ZIP+4.  For the list shown above, the corrected addresses are:

Besides formatting and abbreviation issues, here are some of the corrections made using CDXStreamer:

91 Gurnet Road, Brunswick, Maine (Missing ZIP code)
Correction - 91 GURNET RD, BRUNSWICK, ME 04011-9328

41 Sherman Road, St. Johnsbury, VT 05819 (Incorrect street suffix)
Correction - 41 SHERMAN DR, SAINT JOHNSBURY, VT 05819-9280

46 Cheltingham Ave., Schenectady, New York 12308 (Incorrect ZIP code)
Correction - 46 CHELTINGHAM AVE, SCHENECTADY, NY 12306-5106

200 MacIntyre Road, Pittsburgh, NJ 15237 (Street name misspelled)
Correction - 200 MCINTYRE RD, PITTSBURGH, PA 15237-4035

202 Kings Highway West, Haddonfield, NJ 08033 (Incorrect directional)
Correction - 202 KINGS HWY E, HADDONFIELD, NJ 08033-1905

320 W Ottawa St., Lansing, MN 48922 (Incorrect state abbreviation)
Correction - 320 W OTTAWA ST, LANSING, MI 48933-1590

3810 Forbes Ave., Philadelphia, Pennsylvania 15260 (Incorrect city)
Correction - 3810 FORBES AVE, PITTSBURGH, PA 15260-6995

Street address correction in CDXStreamer follows the US Postal Service "one component failure rule" to find a correct address match.  If not more than one address component mismatch exists, where a "component" is defined as a predirectional, street name, street suffix, or a postdirectional, then a correct address can be returned.  For instance, the address  “500 North Main Street West” has four components.  If one of the components has to be added, changed, or deleted to achieve a unique match, CDXStreamer will correct the address and return the ZIP+4 code.  If more than one component is incorrect, no match is allowed.  Also, no match can be returned if adding, changing or deleting a component results in multiple matches.  The priority is to add a suffix first before adding a directional.  If no match exists, the suffix is changed or deleted, and then the directional.

If there is not an exact match within the city, the "one component failure rule" is applied within the input ZIP code. If there is still no match, the rule is applied to all addresses within the postal finance number, and is only considered invalid if there is no match within that.

For a short tutorial covering address verification and address correction using CDXZipStream, please refer to the YouTube video “Address Correction and ZIP+4 Analysis in Microsoft Excel”.

Our newest version of CDXStreamer provides address correction, ZIP+4, ZIP+4/TIGER, and Canadian postal code data, all within Microsoft Excel.  But to the casual user of the US Postal Service (which covers most of us) ZIP+4 codes seem to be just extraneous information, and in fact, there is certainly no requirement from the USPS to use them when addressing mail.  So why the interest in ZIP+4 codes?

The use of ZIP+4 is intended mostly for business mailers that use computerized or machine-printed labels that can be read by automated scanners.  The 4-digit add-on number identifies a much more specific geographic segment, such as a city block, office building floor, or other high-volume mail receiver, as compared to the 5-digit ZIP code alone, and as a result, helps the USPS deliver mail more efficiently and accurately.  Since it reduces handling and decreases the possibility of mis-delivered mail, mail can get there sooner and even better, can also be eligible for reduced rates.

But ZIP+4 codes also provide a way to access valuable data that can help identify potential customers and markets.  With the millions of ZIP+4 codes assigned in the United States, they can be used to very accurately pinpoint an address and link it to specific demographic information available from the U.S. Census Bureau.  For instance, CDXStreamer can identify the ZIP+4 codes for a list of addresses, and then find the latitude, longitude, census tract, and block number associated with each of those codes.  These data are the keys to the extensive demographics available from the 10-year and annual American Community Surveys performed by the U.S. Census.

CDXStreamer specifically uses a USPS data product that matches the information on the Census Bureau's TIGER File to the United States Postal Service's ZIP+4 database. TIGER (Topologically Integrated Geographic Encoding and Referencing) data is used by the Census Bureau to describe physical land attributes, such as lakes and rivers, as well as other areas such as census tracts, to support the process of taking the decennial census. The resulting USPS file contains over 33 million data records from more than 27,000 5-digit ZIP Codes. The records relate ZIP+4 codes to state, county, Standard Metropolitan Statistical Areas (SMSA) codes, tract number, block number, and geographic coordinate information. Coverage of the file is limited to the address ranges in ZIP+4 database that could be successfully matched to the address ranges in the Census Bureau's TIGER File.

The end result is an effective way to help identify and develop market opportunities using one of the most extensive demographic databases available for the U.S. market.

For a short tutorial showing how CDXStreamer works, see the YouTube video Address Correction and ZIP+4 Analysis in Microsoft Excel.

Also available is a complete listing of all datafeeds and formulas provided with CDXStreamer in the downloadable Excel file CDXStreamer Example Data.

CDX Technologies also has a companion product, CDXZipStream, that provides extensive demographics based on census tract and is available as an add-in in Microsoft Excel.  The CDXZipStream Premium version includes census tract demographics covering population, age, income, household size, educational level, school enrollment, and marital status.  You can download an Excel file with a complete list of available demographics from CDXZipStream here.

We now have a new database for CDXZipStream that provides an extensive variety of demographic data for 2010 census tracts. CDXZipStream is our Excel-based zip code software that performs address, zip code, and geographic analysis, including route optimization, geocoding, zip code radius calculations, address verification, and demographics import.

Demographics based on census tract offers significant advantages over zip code data. Since census tracts are about half the population of zip code areas, the smaller size provides a more accurate demographic snapshot.  Also, zip code census data is actually based on ZCTA's, or Zip Code Tabulation Areas, which the Census Bureau use to approximate zip codes. Census tracts, which are precisely defined by the Census Bureau, do not include the possible error associated with using ZCTA's.

The CDXTract feed provides a variety of data from the 2010 Census and the on-going American Community Survey.  Topics are:

- Age

- Individual Earnings

- Educational Attainment

- Household Size

- Household Income

- Housing Value and Rent

- Marital Status

- Population

- Race

- School Enrollment

- Land, Water Area

For a complete listing and description of each demographic variable, you can download an Excel file which describes each CDXZipStream data feed in detail, including CDXTract.

To access the data, census tract codes (also referred to as FIPS codes), must be provided as input in an Excel worksheet. CDX Technologies now offers a free Excel template that can identify census tract codes for an address list – please see our blog posting Get Census Tract FIPS Codes in an Excel Template.  You can also view a short Youtube tutorial on how to use the template: Get Census Tract FIPS Codes in Microsoft Excel.

Once you have the list of FIPS codes for each location, use your cursor to select the first code in your list, then click on the CDXZipStream icon on the main toolbar. You’ll see a dialog like this:

We’ve selected the CDXTract data feed (under the Select Data Feed option), and have also added the data fields we need to the list on the right, in this case covering median age data and earnings levels.

Click "Get Data", and the selected data will now be automatically returned to the worksheet (only part of the returned data is shown here):

For a short tutorial on how to use the CDXTract data feed, please see the YouTube video Census Tract Demographics for an Address List.

Between the recent 2010 Census and the ongoing American Community Survey, there is now an unprecedented level of extensive, up-to-date demographics data available for the U.S. market.  Please note that census tract data is an additional purchase, and is compatible with all demographic versions of CDXZipStream.  For pricing information and access to the CDXTract database, please contact us at 1-877-CDX-TEC1 (1-877-239-8321) or support@CDXTech.com.

In our Microsoft Excel add-in, CDXZipStream, there are three data feeds (CDXState, CDXCounty, and CDXCBSA) that provide population estimates for their respective geographies for each of the past ten years.  (These data feeds are available in the CDXZipStream Premium and Premium ACS versions.)   These population estimates are developed by the U.S. Census Bureau, and the entire time series of estimates beginning with the most recent decennial census is revised annually. Ultimately these provide an extremely accurate historical picture of population growth trends over the previous decade, a very useful piece of information for many industries ranging from home construction to car sales.

To access these population estimates within Microsoft Excel, select the first worksheet cell that starts your list of geographic locations.  For instance, here is a list of counties in an Excel workbook:

When working with county lists, use the format "County|State", where a vertical bar delimiter is used to separate the county name and state two-letter abbreviation.  When working with state lists, you must also use the state two-letter abbreviation.  For Core-Based Statistical Areas (CBSA’s) use the five-digit designation by the Office of Management and Budget, which can be found on the census website.   For instance, the Akron, Ohio CBSA has the five-digit designation 10420.

After selecting the first cell in the list, click on the CDXZipStream button on the main toolbar, and on the main dialog select the appropriate feed (in this case, CDXCounty) and desired years of estimated data:

Click “Get Data” and the requested data for the last ten years is returned to the worksheet (only partial data is shown here):

You can now use Excel’s calculational and charting capabilities to look for population trends.  For instance, do you need to locate a franchise to take advantage of high population growth within a particular county, state, or CBSA?  The combination of CDXZipStream and Microsoft Excel can quickly help you quantify trends that will allow you to make decisions based on hard, accurate data. 

Many of our clients use the CDXLocateMP function of CDXZipStream (available in the MapPoint, Basic, and Premium demographic versions) to look up the zip or postal code for an address, or to verify that a given code is correct for an address.   Postal code format varies from country to country, and the amount of postal code information available from CDXZipStream and MapPoint varies as well. 

For a detailed description on how to get missing ZIP or postal codes, see our post Getting Zip Codes for an Address List. 

For a short tutorial on how to use the CDXLocateMP function,  please watch this YouTube video "Address Validation in Excel".  

All industrialized countries use postal codes; in the United States we happen to call them ZIP (Zoning Improvement Plan) codes, or ZIP+4 when an additional 4-digit identifier is used.  However, ZIP codes are unique to the United States, and there is not a universal standard among countries.  For example, both Canada and the United Kingdom use alphanumeric codes, which allow for more variation with fewer characters.   

Since the CDXLocateMP function uses MapPoint as its data source, and MapPoint is a stand-alone desktop software, there are limitations on how much postal code data is available for retrieval.  For instance, there are currently over 800,000 Canadian postal codes alone, and it is just not feasible for MapPoint North America or Europe to include them all.  As a result, requesting a postal code for a given address through CDXLocateMP will in some cases (such as Canada) return only a partial code.   Note that for the United States, with only about 40,000 ZIP codes (not including ZIP+4), all five-character codes are included in the MapPoint database and can be returned through CDXLocateMP.

Here is a table showing examples of the postal code format and the data returned for various countries (where MapPoint uses address find):

There are only two instances, Canada and the United Kingdom (both of which use alphanumeric codes) where the entire postal code is not returned from MapPoint.  For example, for the Canadian address that contains the code “L4M 3A5” only the first three characters are returned.  Note that CDXZipStream does have available for purchase a complete database of Canadian postal codes, although this database is not compatible with the MapPoint find address option.  Please see our blog Canadian Postal Code Database - A New Addition to CDXZipStream for more information on use of the Canadian database option.

Remember that when inputting address data in MapPoint or CDXZipStream, it is important to follow standard address conventions which do vary slightly from country to country, as shown in the table above.  In the U.S., Canada, and the U.K. the postal code is listed just prior to the country, while in most European countries the postal code is listed prior to the city.

We’ve just added a new free CDXZipStream template, downloadable from our website, which identifies census tract FIPS codes for a list of address locations.  CDXZipStream is our Microsoft Excel add-in that performs address, zip code, and geographic analysis, including route optimization, geocoding, zip code radius calculations, and address verification.

A FIPS code is a unique 11-digit code assigned to each census tract by the U.S. Census Bureau, and identifying the FIPS code for a given address allows us to tap into the wealth of demographic information available from surveys such as the ten-year U.S. Census and annual American Community Survey.  Since census tracts are on average only about half the size of zip code areas, they provide a much more accurate demographic picture of the population surrounding a given address.  For more information about FIPS, please refer to our recent blog post All About FIPS Codes.

To use the template:  You will need to install CDXZipStream (MapPoint version or higher), Microsoft MapPoint, and also have access to an internet connection.  The template is also compatible with both trial versions of CDXZipStream and MapPoint.   Just copy and paste your list of addresses to the template, and press the button “Get FIPS”.  CDXZipStream works with Microsoft MapPoint to geocode each address (get its latitude and longitude); this data is then used to pinpoint the specific census tract where each address is located, using an FCC application programming interface.  Currently the template is limited to identifying FIPS codes for a maximum of 5000 addresses at a time.  If necessary you can run the template more than once if you have more than 5000 addresses to analyze.

Since CDXZipStream looks to match each listed address to a location in MapPoint, the match quality is also evaluated.  Match types are:

1. Exact - A unique entry was found in MapPoint for this address.

2. Allow Ambiguous - The first of at least two matching entries was found in MapPoint.

3. Best Match - MapPoint did not find a good match, but here is the best of possible alternatives.

4. Zip Code - MapPoint could not find any matches, the centroid of the zip code is returned from CDXZipStream

Since only match types Exact and Allow Ambiguous represent good matches, FIPS codes are only returned for these types.  Address information for other matches should be checked for misspellings, zip code mismatches, or other errors.  Consequently the template provides a method for address verification along with FIPS census tract identification.

CDXZipStream,our Microsoft Excel add-in, contains an array of demographic data that can provide invaluable insight into your current and potential customers.  For example, data such as household size, income, educational attainment, ethnic background, and housing value can be a critical input to your marketing program or business plan.  CDXZipStream demographic versions can provide these fields and more, based on resources such as the 2010 Census, the latest 5-year American Community Survey, and the Census Survey of U.S. Businesses   – please see our version comparison for more information.

Once you get the demographics you need from CDXZipStream, it may also be helpful to map the data to get a visual picture of the results.  In this case we recommend using Microsoft MapPoint, which allows you to easily import data from Excel and provides a wide range of mapping options as well.  

(Note:  MapPoint is available as a 14-day, fully-functional free trial, available for download from the Microsoft website.)

To map your data using MapPoint, click on “Data” on the main MapPoint toolbar at the top of the screen, and then select “Import data wizard …” from the drop-down list.  From here you can select the Excel file that contains the demographic data of interest; you can also select other types here such as text or Access database files.  After selecting the desired file, you will also be asked to select the appropriate worksheet (assuming the file was an Excel workbook). Then click “Next”, and you’ll see a MapPoint dialog like this:

From here the mapping parameters can be specified.  First select the applicable country or region, as well as whether the first row contains column headings.   Under each column shown in the dialog, you will also need to specify the data type for each column of data.  For example, the first column in the screenshot above is the list of zip codes that forms the basis of the demographic data, and so select “ZIP Code” from the drop-down list of data types. At least one column of data like this must be available in order to geo-locate the data on the map.  For the other columns of data, you can specify “Name” (to help identify the area or pushpin on the map), “Other Data” (which will provide the numeric value), or “Skip Column” (to exclude this data altogether).  Note that if multiple columns of data are identified as “Other Data” all values will be used in the mapping analysis.  Since data will overlap for the same location, these maps may be difficult to read; consider creating separate maps for each column of data as an alternative.

In the example above we want to map median age by zip code.  The first column of zip codes is used to geo-locate the data, so we select “ZIP Code” as the data type, and we also select median age as the “Other Data” type.  The column containing store number information can be used as the “Name” type so this data can be used to identify each point provided on the map.  All other columns of data are specified as “Skip Column”.  After clicking on “Finish”, all we need to do is specify the map type:

To create a map showing circles that are sized relative to the median age value, we click on “Sized Circle”, and the result is:

Passing the cursor over one of the circles will show the zip code and median age value.  Double clicking on the same circle will also show the store number value. 

Once the map has been created, you can also use MapPoint right-click features for each point.   Use your mouse to right-click on one of the circles to show or hide data, include the point in a route, or even create a drive-time zone around the point, like this: 

MapPoint can be an extremely powerful tool to help visualize your geographic data and support your business needs.  Consider evaluating the free trial of MapPoint the next time your get demographic data from CDXZipStream.  We also recommend MP2K as a reputable reseller of MapPoint software. 

We were recently asked by a customer if it is possible to use CDXZipStream, our Microsoft Excel add-in for address and zip code analysis, to determine whether a zip code is in a rural area.  Here’s our response:

A fairly stringent determination of rural zip codes, which can be done easily using CDXZipStream, is based on whether the zip code is part of a Core Based Statistical Area, or CBSA. CBSA’s are defined by the Office of Management and Budget as urban areas; a metropolitan area contains a core urban area of 50,000 or more population, and a micropolitan area contains an urban core of at least 10,000 (but less than 50,000) population. Each metro or micro area consists of one or more counties and includes the counties containing the core urban area, as well as any adjacent counties that have a high degree of social and economic integration (as measured by commuting to work) with the urban core. Based on the 2010 Census, approximately 95% of the U.S. population lives in either a micropolitan or metropolitan CBSA.   If a zip code is not associated with a CBSA then, it is a good assumption that it is in a non-urban, or rural area. Using the CDXZipCode premium feed (available in CDXZipStream Demographic version and higher), request the data field “CBSA”; if the returned value is "N/A", then the zip code is not associated with any CBSA and you can assume that it is rural.

The attached map shows the current coverage of Core Based Statistical Areas in the U.S. and Puerto Rico:

Source: http://www2.census.gov/geo/maps/metroarea/us_wall/Dec_2009/cbsa_us_1209_large.gif

Using an alternative U.S. Census Bureau definition, rural areas comprise open country and settlements with fewer than 2,500 residents.  All other (urban) areas are of two types—urbanized areas and urban clusters—identical in the criteria used to delineate them but different in size. The Census Bureau defines an urbanized area wherever it finds an urban nucleus of 50,000 or more people. They may or may not contain any individual cities of 50,000 or more.  Urban clusters have a population of at least 2,500 but less than 50,000 persons.  Using these criteria, about 16% of the U.S. population lives in what is considered to be rural areas, based on the results of the 2010 Census.  You can download from the Census website relationship files showing which ZCTA’s (the Census Bureau’s approximation of zip codes) correspond to urban and non-urban (rural) areas.

You may also want to consider population density as a rough measurement of urban/rural characteristics, Population density can be easily calculated by dividing the total population of the zip code by the land area.  Again, using the CDXZipCode Premium feed, request the data fields “Population” and “LandArea” and then use Microsoft Excel to perform the calculation.  Keep in mind, however, that population density provides only a partial picture, since there may be non-residential areas with low population counts that exist in the middle of urban centers.