Convert Latitude Longitude to Address in Excel

You convert latitude and longitude coordinates into an address because it is easier for humans to understand an address than coordinates. For instance, ‘123 Main Street, New York’ is far more intuitive than ‘40.7128, -74.0060.’

When you convert latitude and longitude coordinates into a human-readable address you are reverse geocoding. It is the opposite of geocoding, which converts an address into latitude and longitude coordinates.

Note: If an address has no name, the reverse geocoding engine may label the location as ‘unknown,’ identify the nearest landmark, or simply return the latitude and longitude coordinates.

In this tutorial, I will show you how to convert latitude and longitude coordinates to an address in Excel using the FILTERXML function and a User-Defined Function created in Excel VBA. 

Method #1: Using FILTERXML and WEBSERVICE Function

You can use the FILTERXML function with the WEBSERVICE function to perform reverse geocode in Excel.

The FILTERXML function extracts specific address data from XML content. It applies an XPath query to the XML data and returns the matched result.

Note: XPath is the language for navigating through elements and attributes in XML content.

The syntax of FILTERXML function:

FILTERXML(xml, xpath)

• xml is required and is a string in valid XLM format. This is the XML data from which you want to extract addresses.
• xpath is required and is a string representing an XPath query.

The WEBSERVICE function retrieves data from a web server using the specified URL. We use the WEBSERVICE function within the FILTERXML function to supply data for the ‘xml’ argument.

Suppose you have the below list of latitude and longitude coordinates of various landmark locations on Earth and want to convert them to human-readable addresses.

You can do the conversion using the below formula in column C.

=FILTERXML(WEBSERVICE("https://nominatim.openstreetmap.org/reverse?lat="&A2&"&lon="&B2&"&format=xml"),"/reversegeocode/result")

How the Formula Works

1. The ‘WEBSERVICE(“https://nominatim.openstreetmap.org/reverse?lat=”&A2&”&lon=”&B2&”&format=xml”)’ segment of the formula sends a request to the Nominatim OpenStreetMap API using the latitude and longitude values in cells A2 and B2 respectively. The segment specifies that the API returns the result in XML format.
2. The XPath “/reversegeocode/result” targets the <result> node within the <reversegeocode> root node of the XML document. The FILTERXML function returns the value of this node, which contains the human-readable address.

Also read: Convert Latitude Longitude to Decimal Degrees in Excel

Method #2: Using VBA to Perform Reverse Geocode in Excel

You can use a custom User-Defined Function (UDF) created in VBA to convert latitude and longitude coordinates into human-readable addresses.

Suppose you have the below list of latitude and longitude coordinates of various landmark places on Earth and want to convert them to human-readable addresses.

Here’s how you can do it using a UDF:

1. Copy the below function code to a standard VBA module.

Function GEOCODEREVERSE(latitude As Double, longitude As Double) As String
On Error GoTo ErrorHandler
Dim xD As New MSXML2.DOMDocument
Dim URL As String
xD.async = False
URL = "https://nominatim.openstreetmap.org/reverse?lat=" & CStr(latitude) & _
      "&lon=" & CStr(longitude)
xD.Load URL
If xD.parseError.ErrorCode <> 0 Then
    HandleError xD.parseError.reason
Else
    xD.SetProperty "SelectionLanguage", "XPath"
    Dim location As MSXML2.IXMLDOMElement
    Set location = xD.SelectSingleNode("/reversegeocode/result")
    If location Is Nothing Then
        HandleError xD.XML
    Else
        Application.Caller.Font.ColorIndex = vbOK
        GEOCODEREVERSE = location.Text
    End If
End If
Exit Function
ErrorHandler:
HandleError Err.Description
End Function
Sub HandleError(errorMessage As String)
Application.Caller.Font.ColorIndex = vbErr
GEOCODEREVERSE = errorMessage
End Sub

2. In VB Editor, open the Tools menu and select References. 

The above step opens the References – VBA Project feature.

3. On the References – VBA Project feature, scroll down, select ‘Microsoft XML, v3.0,’ and click OK.

4. Save the workbook as a Macro-Enabled Workbook (*.xlsm) to retain the UDF for future use.
5. Switch to the workbook containing the dataset of latitudes and longitudes.
6. Enter the formula below in column C.

=GEOCODEREVERSE(A2,B2)

The GEOCODEREVERSE formula returns the addresses of the target latitude and longitude coordinates in column C.

Note: The UDF may take time to generate addresses for all the latitude and longitude coordinates. Excel might seem unresponsive, but the UDF is working in the background. Please be patient and allow the process to complete.

Save the UDF in a Personal Macro Workbook

You can save the UDF in a Personal Macro Workbook (PERSONAL.XLSB) to make it available to all your workbooks.

Here’s how to do it:

1. In the Project Explorer window of the VB Editor, select ‘VBAProject (PERSONAL.XLSB)’ and insert a module.

Note: If you don’t see the Project Explorer window, press CTRL + R to open it. If you don’t see ‘VBAProject (PERSONAL.XLSB)’ you can open it by recording an empty macro and selecting the Personal Macro Workbook option on the ‘Store macro in’ drop-down menu.

2. Copy to the module the function code shown in the section above.
3. Set the reference to ‘Microsoft XML, v3.0’ as shown in the above section.
4. You can use the UDF in any workbook but must prefix it with ‘PERSONAL.XLSB!’ as shown in the example below.

=PERSONAL.XLSB!GEOCODEREVERSE(A2,B2)

Comparison of a UDF and the FILTERXML Formula

• A UDF consolidates a complex formula into a single easy-to-understand function call. For example, instead of you using =FILTERXML(WEBSERVICE(“https://nominatim.openstreetmap.org/reverse?lat=”&A2&”&lon=”&B2&”&format=xml”), “/reversegeocode/result”) you can use GEOCODEREVERSE(A2,B2).
• A UDF can include custom error messages and handle issues like invalid coordinates, API failures, or connectivity problems. Unlike the FILTERXML formula, which returns uninformative errors like #VALUE!, a UDF can display meaningful messages or default values.
• A UDF can be tailored to return specific components of the reverse geocoded result, such as the city, state, or country. The FILTERXML formula returns the full reverse geocoded result per the XPath query.
• A FILTERXML formula is ideal for small-scale tasks with only a few lookups, especially when you want to avoid using macros in your workbook.

How Reverse Geocoding Works

You might be curious about how reverse geocoding works. Here’s how it works:

1. The process starts with input of latitude and longitude coordinates representing a specific point on the Earth’s surface.
2. The coordinates are used to search a geographic database containing mapping information such as streets and roads, buildings, and administrative areas such as cities. 

The queried databases can come from public sources like OpenStreetMap (the one we used in this tutorial), or proprietary sources like Google Maps and Bing Maps where you must sign up with card information for an API key.

3. The reverse geocoding engine identifies the closest known location to the given coordinates. 
4. The reverse geocoding engine returns a human-readable description of the location.

You can apply reverse geocoding in the following areas:

• To show the address of a selected point on a map.
• Find a drop-off or pickup location.
• Convert GPS coordinates from mobile devices to human-readable addresses.
• Tag photos or social media posts with a location name.

I have shown you how to convert latitude and longitude coordinates to an address in Excel using the FILTERXML function and a UDF. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• How to Separate Address in Excel?
• Extract ZIP Code from Address in Excel
• Remove Middle Name from Full Name in Excel