Microsoft Excel “custom functions” can do a variety of things related to address and zip code information, such as importing demographic data, finding zip codes within a radius, even calculating driving time and distance. Custom functions (also called User-Defined Functions or UDFs) perform complicated calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, or LOOKUP. In this article we will show you how to use custom functions, in conjunction with Microsoft MapPoint, to check the validity of street addresses.
Incorrect address information is a common problem. The street or city names can be misspelled, the zip code may not match the city information, or the street number may be invalid. You can use a web-based program like MapQuest or Google Maps to check the validity of a single address, but for multiple addresses in a mailing list or delivery route, the best approach is to use an automated program that can check large lists of data all at one time.
An Excel custom function working in conjunction with a stand-alone mapping program like Microsoft MapPoint can validate long lists of addresses automatically and return the results directly to your worksheet. Since all interactions with MapPoint occur in the background, you can work completely within the Excel environment – there’s no need to learn a new application. For example, to check the validity of an address in worksheet cell A1, you can input the appropriate custom function formula (inserted in cell B1, for instance) which would look something like this: “= CustomFunction (A1)”. If the address appears to be valid, the “best match” address is returned to cell B1. If no match is found, the message “Invalid address” is returned. MapPoint also has the capability to provide best match information even when there is a minor error in the address. In cases where there is a city or street misspelling, or transposed numbers in the zip code, the best match returned to the worksheet will have these errors corrected.
For a long list of addresses in column A, the custom function formula can be copied and pasted as needed in column B, so you can automatically validate many addresses without the time-consuming manual input required for other mapping programs.
It is also possible to specify that other types of data be returned to the worksheet if the address is determined to be valid. For example, geocode information for the address, such as latitude and longitude, can be returned. Street name, city, state, zip code, or country information can also be returned, providing a reliable way to parse the address into separate worksheet cells.
This is a good example of how Excel custom functions can use other applications like Microsoft MapPoint to automate specialized tasks and calculations, with no need to learn a new application or even leave the familiar environment of Excel. From checking the validity of address information to identifying zip codes within a radius, custom functions can be valuable tools for analyzing zip code and other location-based information.
Leave a Reply