Repository files navigation

Download Geocode.xlsm.

This is an Excel file (with a Visual Basic macro function) that converts addresses to latitudes and longitudes (geocoding), and latitude-longitudes to addresses (reverse geocoding). This works through public geocoding APIs.

• It has been tested on Office 2017 and Office 365 on Windows.
• It requires Excel 2013 or above (for theWorksheetFunction.EncodeURL method)
• It only works on Windows. TheMSXML2 reference we use to access the APIs is not available on Mac.

The Nominatim API uses OpenStreetMap, which is not as comprehensive as Google's but is free. See:https://nominatim.org/release-docs/develop/

=NominatimGeocode(address) Converts a string address (e.g. "10 Downing Street" or "Tokyo, Japan") into a "latitude,longitude" string=NominatimReverseGeocode(lat, lng) Converts a latitude and longitude into an address

The Google Geocoding API is comprehensive, but requires an API key. See:https://developers.google.com/maps/documentation/geocoding/get-api-key

• =GoogleGeocode(address, api_key) Converts a string address (e.g. "10 Downing Street" or "Tokyo, Japan") into a "latitude,longitude" string
• =GoogleReverseGeocode(lat, lng, api_key) Converts a latitude and longitude into an address

The usage is otherwise identical to the Nominatim functions.

To get started, download and openGeocode.xlsm.

If you see a security warning, click on "Enable Content".

In any cell, type in the formula=NominatimGeocode("10 Downing Street, London") in any cell. After a second, it shows the result as51.50344025,-0.127708209585621 — which is the<latitude>,<longitude> combination. Here's a live example:

Visit thelist of the largest cities and copy the table, along with its population into Excel.

If all the city names are in column A, you can create a new column with the formula=NominatimGeocode(A2) (replacingA2 with the actual cell reference).

Here, we useA2&", "&B2 to concatenate the city and nation. The first cell will look up “Chongqing, China”. When you press Enter, the result is:

Copy the formula into the entire column.

Automatic calculations are disabled to reduce the number of API requests. So, if you copy-paste or drag a formula, the formula is not rerun. The values are copied as-is.

Instead, select asmall group of cells and pressCtrl-Q to run the RefreshSelected macro. (Each lookup takes 0.5 - 1 seconds. Select batches of 10-20 cells at a time.)

Once you finish geocoding, replace the formulas with the values. Otherwise, Excel will re-runthe entire geocoding every time you save, open, or refresh the file. To do this, copy the geocoded cells (Ctrl-C) and Paste Special > Values (Alt-H-V-S-V).

This geocodes the selected cell range. Geocoded cells turn blue. But if there’s an error, these cells turn red.

Errors occur if the location contains special characters (e.g. the “ã” in São Paulo). In such cases, re-write the location in ASCII, i.e. without special characters (e.g. Sau Paulo.)

To convert this single column with<latitude>,<longitude> into one column each, you can use Data >Text to Columns, and split the column using comma, as the separator.

The result is an Excel sheet with 2 columns: Latitude and Longitude.

1. Geocode theairport locations in the UK
2. Geocode thepost office locations in Singapore

If you prefer using Google's Geocoders on Google Sheets, use:

• Geocoding Sheet byChristos Sameras. Copy it and use it.
• Geocoding Marketplace App.Install it to use in Google Sheets
• Geocoding Macro byMax Vilimpoc. Add it to your Google Sheet.