We’ve updated our Geocoding spreadsheet which makes it a breeze to quickly calculate latitude and longitude information for a long list of addresses. Simply enter your address information in separate columns (address, city, state and postal code) and press “Get Coordinates”. The template uses CDXZipStream and Microsoft MapPoint (which must be installed on your system).
You can download the template from our website. You can also download it by becoming a fan of CDXZipStream on Facebook. Just click on the “For our Fans” tab to get access to the template. If you don’t have Microsoft MapPoint, you can download a free trial at Microsoft.com. A free trial of CDXZipStream is also available.
This application illustrates using CDXZipStream directly rather than using custom functions. All of the functions used for geolocation, CDXLocateMP and CDXZipCode can be called directly in Visual Basic for Applications (VBA). In Excel, this has the advantage that we can use a macro to control how the calculations are made. These same techniques can be used from other applications. This includes Microsoft Office application (Word, Access, etc.) as well as others that support VBA.
The macro combines the columns into a single address and then searches MapPoint to find a best match. First it will search for an exact match. If this fails then it will search for an ambiguous match (two or more good results) and return the top one. For the situation where both these options fail it will then return the best of the alternatives MapPoint has available or if all of the above fail it will return the data for the zip code of the address. The type of match appears to the right of the returned latitude/longitude data. This would be very complex to do using custom functions for most users.
A status bar in the lower left summarize the percentage of the calculations that are completed. For very long address lists that can take a lot of time you can stop the calculation by pressing the ESC key. You can update lists that are ten of thousands long which is difficult to do with other free geocoder applications on the web.
For a short tutorial on the geocoding template, please see the video below:
The YouTube version can be found at: Get Latitude and Longitude for Addresses in Excel