One valuable yet seldom used feature of Microsoft Excel is "Custom Functions". Also known as UDF's (or User-Defined Functions) they can do many unique tasks. Examples include looking up zip codes, calculating driving time and driving distance, identifying addresses within a radius, even performing driving route optimization. Custom functions can perform complex calculations and are used just like standard Excel formulas SUM or AVERAGE
In this article, we will describe how you can custom functions to create lists of zip codes (by city, county, or state) in Microsoft Excel. These lists can be used for a variety of purposes, such as location based marketing, and planning direct mail or advertising campaigns. But instead of cutting and pasting zip codes from a website or database, custom functions in Excel can automate interactions with a database and import lists easily into a worksheet, allowing the user to then analyze the data using Excel's powerful built-in capabilities. From the user's point of view, these custom functions look just like another formula in the worksheet. Since all interactions with the database occur in the background, the user can work entirely within the familiar Excel environment. There's no need to learn a new application.
How would this work within an Excel worksheet? Let's say we need a list of all the zip codes in Randolph Township, in Morris County, New Jersey. With our CDXZipStream product you can use a custom function called CDXZIPLIST. We just type this formula into a cell as follows"= CDXZIPLIST ("New Jersey", "Morris", "Randolph")". The function we call CDXZIPLIST gets the required data from a local database and returns all the Randolph codes to the worksheet cell. If we need data for a long list of cities in a worksheet, we can also input a formula using the only worksheet cell locations, such as "= CDXZIPLIST (A1, B1, C1)", where A1, B1, and C1 refer to the cell locations of the state, county, and city, respectively. We can then easily copy and paste this formula to apply it to all the cities in our list. The zip codes returned can be output within a single cell as text or as Excel Formula Arrays in multiple cells.
CDXZipStream also has a "right-click" function to lead you thru entering the parameters of the CDXZipList function. Here is the dialog that appears
You select the state first which then presents the list of available counties. Choose the county and then press from the options for cities in that county. After selecting the state press "OK" to enter the formula.
Lists for a county or state can also be generated using this method. For all zip codes in the state of California, the custom function formula is"'= CDXZIPLIST ("California"). The omission of county and city information in this case indicates only state-level information is required.
Once you have the list of zip codes, you can add additional Custom Formulas to extend your analysis. Examples of this include calculating the population of all zip codes in your county or knowing the driving distance of a central point to all zip codes in your state. These are good examples of how Excel custom functions can automate your work, with no need to learn a new application or even leave the familiar environment of Microsoft Excel. From calculating driving distance to generating zip code lists in Microsoft Excel, custom functions can be valuable tools for analyzing address information.
The video below shows how this all works:
Using Zip Code Lists in Microsoft ExcelCDXZipStream,is an Excel add-in that provides zip code data, demographics by zip code, route optimization, zip code radius analysis, geocoding, and mapping using custom functions. A free, 30-day trial version is available for download from the website, as well as example spreadsheets and tutorials showing how to use Excel custom functions in a variety of applications.