Microsoft Excel’s “custom functions” can do a variety of things related to address and postal code information, such as import demographic data, find postal codes within a radius, and even calculate 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 SUM, AVERAGE, or SEARCH functions. In this article, we will show you how to use custom functions, in conjunction with Microsoft MapPoint, to verify the validity of postal addresses.
Incorrect address information is a common problem. The names of the streets or cities may be misspelled, the postal code may not match the city information, or the street number may not be valid. You can use a web-based program such as MapQuest or Google Maps to verify the validity of a single address, but for multiple addresses on a mailing list or delivery route, the best approach is to use an automated program that can verify large lists of addresses. data. everything at once.
A custom Excel function that works in conjunction with a separate mapping program such as Microsoft MapPoint can automatically validate long lists of addresses and return the results directly to your worksheet. Since all interactions with MapPoint occur in the background, you can work entirely within the Excel environment; no need to learn a new application. For example, to check the validity of an address in cell A1 of the worksheet, you could enter the appropriate custom function formula (inserted in cell B1, for example) that would look like this: “= Custom function (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 ability to provide the best match information even when there is a minor error in the address. In cases where there is a misspelled city or street, or numbers transposed in the zip code, the best match returned to the worksheet will have these errors corrected.
To get 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 for other programs mapping.
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, geocoding information can be returned for the address, such as latitude and longitude. Information about street name, city, state, zip code, or country can also be returned, providing a reliable way to parse the address in separate worksheet cells.
This is a good example of how Excel custom functions can use other applications such as Microsoft MapPoint to automate specialized calculations and tasks, without the need to learn a new application or even leave the familiar Excel environment. From verifying 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.
