How to Perform Reverse Geocoding in Excel: 5 Methods

Method 1 – Open Microsoft Visual Basic Application from Developer Tab

Go to the Developer tab of the ribbon
From the Code group, select Visual Basic.

You will see the Visual Basic window.
From the Insert tab, choose Module.

A module will look like the following image after opening, where you can write the code.

Method 2 – Select Necessary Reference from Tool Option

From the Tools tab of the VBA window, select References.

You will see a list of available references in the References – VBAProject dialog box.
Select Microsoft XML, v3.0, and make sure the reference is marked.
Press OK.

Method 3 – Use Code to Build Required Custom Function

Copy the following code and paste it into the module from the first step.
We named the custom functions as ReverseGecoder.

Option Explicit
Function ReverseGeocoder(lati As Double, longi As Double) As String
On Error GoTo 0
Dim xD As New MSXML2.DOMDocument
Dim URL As String, vbErr As String
xD.async = False
URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
"&lon=" + CStr(longi)
xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
"&lon=" + CStr(longi))
If xD.parseError.ErrorCode <> 0 Then
Application.Caller.Font.ColorIndex = vbErr
ReverseGeocoder = xD.parseError.reason
Else
xD.SetProperty "SelectionLanguage", "XPath"
Dim loca As MSXML2.IXMLDOMElement
Set loca = xD.SelectSingleNode(" / reversegeocode / result")
If loca Is Nothing Then
Application.Caller.Font.ColorIndex = vbErr
ReverseGeocoder = xD.XML
Else
Application.Caller.Font.ColorIndex = vbOK
ReverseGeocoder = loca.Text
End If
End If
Exit Function
0:
Debug.Print Err.Description
End Function

After pasting the code save it, and go back to the worksheet with the coordinates.

Method 4 – Implement Custom Function

In your Excel sheet, type =REV in cell D5 and you will see the auto-suggestion of the custom function from the previous step.
Select the function press Tab on the keyboard or double-click on the function using the mouse.

Implement the function type the following formula in cell D5.

=ReverseGeocoder(B5,C5)

Press Enter and see the desired location for the coordinates of cells B5 and C5.

Method 5 – Show Final Result

The AutoFill feature in the following image is if you take your cursor at the lower right corner of cell D5.
Drag the feature to the lower to implement the formula in the lower cells as well.

You will be able to see all the locations by using the custom functions which work as a reverse geocoder.

Things to Remember

If you don’t activate the reference before implementing the custom function, it won’t show the desired result.
Your Excel file might act slower after implementing the function, so keep patience after using AutoFill.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Reverse Geocoder.xlsm

<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Function ExtractCityNames(inputTextFromUDF As String) As String Dim inputText As String Dim splitText() As String Dim cityName As String Dim i As Integer inputText = inputTextFromUDF splitText = Split(inputText, vbCrLf) For i = 0 To UBound(splitText) splitText(i) = Trim(splitText(i)) Dim parts() As String parts = Split(splitText(i), ",") If IsNumeric(Trim(parts(UBound(parts) - 1))) Then cityName = Trim(parts(UBound(parts) - 2)) Else cityName = Trim(parts(UBound(parts) - 1)) End If ExtractCityNames = ExtractCityNames & cityName & vbCrLf Next i End Function Function ReverseGeocoder(lati As Double, longi As Double) As String On Error GoTo 0 Dim xD As New MSXML2.DOMDocument Dim URL As String, vbErr As String xD.async = False URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _ "&lon=" + CStr(longi) xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _ "&lon=" + CStr(longi)) If xD.parseError.ErrorCode <> 0 Then Application.Caller.Font.ColorIndex = vbErr ReverseGeocoder = xD.parseError.reason Else xD.SetProperty "SelectionLanguage", "XPath" Dim loca As MSXML2.IXMLDOMElement Set loca = xD.SelectSingleNode(" / reversegeocode / result") If loca Is Nothing Then Application.Caller.Font.ColorIndex = vbErr ReverseGeocoder = xD.XML Else Application.Caller.Font.ColorIndex = vbOK ReverseGeocoder = loca.Text End If End If Exit Function 0: Debug.Print Err.Description End Function

Option Explicit Function ReverseGeocoder(lati As Double, longi As Double) As Variant On Error GoTo 0 Dim xD As New MSXML2.DOMDocument Dim URL As String, vbErr As String xD.async = False URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _ "&lon=" + CStr(longi) xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _ "&lon=" + CStr(longi)) If xD.parseError.ErrorCode <> 0 Then Application.Caller.Font.ColorIndex = vbErr ReverseGeocoder = xD.parseError.reason Else xD.SetProperty "SelectionLanguage", "XPath" Dim loca As MSXML2.IXMLDOMElement Set loca = xD.SelectSingleNode("/reversegeocode/result") If loca Is Nothing Then Application.Caller.Font.ColorIndex = vbErr ReverseGeocoder = xD.XML Else Application.Caller.Font.ColorIndex = vbOK ReverseGeocoder = SplitStringToArray(loca.Text) End If End If Exit Function 0: Debug.Print Err.Description End Function Function SplitStringToArray(strInput As String) As Variant Dim arrResult As Variant Dim i As Integer arrResult = Split(strInput, ", ") For i = LBound(arrResult) To (UBound(arrResult) - 1) / 2 Dim temp As String temp = arrResult(i) arrResult(i) = arrResult(UBound(arrResult) - i) arrResult(UBound(arrResult) - i) = temp Next i If Not IsNumeric(arrResult(1)) Then ReDim Preserve arrResult(UBound(arrResult) + 1) For i = UBound(arrResult) - 1 To 1 Step -1 arrResult(i + 1) = arrResult(i) Next i arrResult(1) = "" End If SplitStringToArray = arrResult End Function

Option Explicit Function ReverseGeocoder(lati As Double, longi As Double) As Variant On Error GoTo 0 Dim xD As New MSXML2.DOMDocument Dim URL As String, vbErr As String xD.async = False URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _ "&lon=" + CStr(longi) xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _ "&lon=" + CStr(longi)) If xD.parseError.ErrorCode <> 0 Then Application.Caller.Font.ColorIndex = vbErr ReverseGeocoder = xD.parseError.reason Else xD.SetProperty "SelectionLanguage", "XPath" Dim loca As MSXML2.IXMLDOMElement Set loca = xD.SelectSingleNode("/reversegeocode/result") If loca Is Nothing Then Application.Caller.Font.ColorIndex = vbErr ReverseGeocoder = xD.XML Else Application.Caller.Font.ColorIndex = vbOK ReverseGeocoder = SplitStringToArray(loca.Text) End If End If Exit Function 0: Debug.Print Err.Description End Function Function SplitStringToArray(strInput As String) As Variant Dim arrResult As Variant Dim i As Integer arrResult = Split(strInput, ", ") Dim numRows As Long numRows = UBound(arrResult) - LBound(arrResult) + 1 Dim result() As Variant ReDim result(1 To numRows, 1 To 1) For i = LBound(arrResult) To UBound(arrResult) result(i + 1, 1) = arrResult(i) Next i SplitStringToArray = result End Function

Sub PopulateReverseGeocoding() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row For i = 3 To lastRow Dim lat As Double Dim lon As Double lat = ws.Cells(i, "B").Value lon = ws.Cells(i, "C").Value ws.Cells(i, "D").Value = ReverseGeocoder(lat, lon) Next i End Sub Function ReverseGeocoder(lati As Double, longi 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(lati) + _ "&lon=" + CStr(longi) xD.Load (URL) If xD.parseError.ErrorCode <> 0 Then ReverseGeocoder = xD.parseError.reason Else xD.SetProperty "SelectionLanguage", "XPath" Dim loca As MSXML2.IXMLDOMElement Set loca = xD.SelectSingleNode("/reversegeocode/result") If loca Is Nothing Then ReverseGeocoder = "No location found." Else ReverseGeocoder = loca.Text End If End If Exit Function ErrorHandler: ReverseGeocoder = "Error occurred: " & Err.Description End Function

Option Explicit Function ReverseGeocoder(lati As Double, longi As Double) As String On Error GoTo ErrorHandler Dim xD As New MSXML2.DOMDocument Dim URL As String Dim house As String Dim road As String Dim city As String Dim state As String Dim postcode As String xD.async = False URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + "&lon=" + CStr(longi) xD.Load (URL) If xD.parseError.ErrorCode <> 0 Then ReverseGeocoder = "Error: " & xD.parseError.reason Else xD.SetProperty "SelectionLanguage", "XPath" Dim houseNode As MSXML2.IXMLDOMElement Dim roadNode As MSXML2.IXMLDOMElement Dim cityNode As MSXML2.IXMLDOMElement Dim stateNode As MSXML2.IXMLDOMElement Dim postcodeNode As MSXML2.IXMLDOMElement Set houseNode = xD.SelectSingleNode("//addressparts/house_number") Set roadNode = xD.SelectSingleNode("//addressparts/road") Set cityNode = xD.SelectSingleNode("//addressparts/city") Set stateNode = xD.SelectSingleNode("//addressparts/state") Set postcodeNode = xD.SelectSingleNode("//addressparts/postcode") If Not houseNode Is Nothing Then house = houseNode.Text End If If Not roadNode Is Nothing Then road = roadNode.Text End If If Not cityNode Is Nothing Then city = cityNode.Text End If If Not stateNode Is Nothing Then state = stateNode.Text End If If Not postcodeNode Is Nothing Then postcode = postcodeNode.Text End If If house = "" Or road = "" Or city = "" Or state = "" Or postcode = "" Then ReverseGeocoder = house & " " & road & ", " & city & ", " & state & ", " & postcode & " [Incomplete!]" Else ReverseGeocoder = house & " " & road & ", " & city & ", " & state & ", " & postcode End If End If Exit Function ErrorHandler: ReverseGeocoder = "Error: " & Err.Description End Function

12 Comments

Khaled Feb 1, 2024 at 5:20 PM

Hi Araf, thank you for sharing Knoledge.

is it possible to get only the city name? what should i change in the code?

Lutfor Rahman Shimanto Feb 5, 2024 at 3:02 PM

Hello KHALED

Thanks for your nice words. Your appreciation means a lot to us. You only want to get the city name from lat and long values.

I am delighted to inform you that I have developed another VBA User-defined function named ExtractCityNames that will extract the city names by taking input from the User-defined function mentioned in this article.

Follow these steps:

1. Press Alt+F11.

2. Click on Insert followed by Module.

3. Paste the following code in the Module and save it.

4. Return to the sheet and select cell D3.

5. Insert the following formula and hit Enter.

=ExtractCityNames(ReverseGeocoder(B3,C3))

6. Finally, copy the formula down using the Fill Handle icon.

Things to Remember: The return time can be longer than usual as we use complex Excel VBA User-defined functions.

I am attaching the solution workbook for better understanding. Hopefully, the idea will fulfil your goal. Stay Blessed.

DOWNLOAD SOLUTION WORKBOOK

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

Peter Berger Apr 16, 2024 at 6:14 PM

Great Job, thanks!

I would like to have all information in different fields, e.E. Country, Street, House number, etc. Especially I need the Country values. Any idea?

Peter

Lutfor Rahman Shimanto Apr 18, 2024 at 1:45 PM

Hello Peter Berger

Thanks for your nice words. Your appreciation means a lot to us.

You wanted to get all the address information in different columns. To do so, I have developed another assistive VBA user-defined function and improved the existing user-defined function named ReverseGeocoder. Using these two functions, you can quickly fulfil your goal.

Press Alt+F11.
Click on Insert followed by Module.

Paste the following improved code in the module and save it:

Return to the sheet and select the intended cell.
Insert the following formula: =ReverseGeocoder(B5,C5)
Hit Enter to see an output like the following GIF.
Now, drag the Fill Handle icon to copy the formula down.

I hope you have found the VBA user-defined function helpful. I have attached the solution workbook; good luck.

Peter Berger Apr 16, 2024 at 6:34 PM

The extraction of cities work not well:

The identified city here is not “Marmara Bölgesi”, it’s Istanbul!
41.03 28.96 Kadir Has Caddesi, Küçükmustafapaşa, Yavuz Sultan Selim Mahallesi, İstanbul, Fatih, İstanbul, Marmara Bölgesi, 34083, Türkiye “Marmara Bölgesi”

The identified city here is not “France métropolitaine”, it’s Troyes!
48.30 4.08 55, Rue de la Cité, Quartier de la Cité, Troyes, Aube, Grand Est, France métropolitaine, 10000, France “France métropolitaine”

Additional to my last message it would better to take the nominatim results and use the field like in the second block, the block of the answer, for single fields in Excel. Then no errors would be possible.

Example of an answer from nominatim:

25b, Bauherrenstrasse, Höngg, Kreis 10, Zurich, District Zurich, Zurich, 8049, Switzerland

25b
Bauherrenstrasse
Höngg
Zurich
District Zurich
Zurich
CH-ZH
8049
Switzerland
ch

Kind regards

Peter
- Reply
  Lutfor Rahman Shimanto Apr 18, 2024 at 2:51 PM
  Dear Peter Berger
  
  Thanks for your feedback. Perhaps you are right about using the direct results from Nominatim, which would lead to more accurate outcomes. However, we have to get the result from the response from the Nominatim API.
  
  In addition to your previous post, you wanted to get all the information in a column but in different cells. Do not worry! I have improved the previously given code by using a 2D array.
  
  SOLUTION Overview:
  
  Required Excel VBA User-Defined Functions:
```
Option Explicit

Function ReverseGeocoder(lati As Double, longi As Double) As Variant
    On Error GoTo 0
    
    Dim xD As New MSXML2.DOMDocument
    Dim URL As String, vbErr As String
    
    xD.async = False
    URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
            "&lon=" + CStr(longi)
    
    xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
            "&lon=" + CStr(longi))
    
    If xD.parseError.ErrorCode <> 0 Then
        Application.Caller.Font.ColorIndex = vbErr
        ReverseGeocoder = xD.parseError.reason
    Else
        xD.SetProperty "SelectionLanguage", "XPath"
        Dim loca As MSXML2.IXMLDOMElement
        Set loca = xD.SelectSingleNode("/reversegeocode/result")
        
        If loca Is Nothing Then
            Application.Caller.Font.ColorIndex = vbErr
            ReverseGeocoder = xD.XML
        Else
            Application.Caller.Font.ColorIndex = vbOK
            ReverseGeocoder = SplitStringToArray(loca.Text)
        End If
    End If
    
    Exit Function
    
0:
    Debug.Print Err.Description
    
End Function

Function SplitStringToArray(strInput As String) As Variant
    
    Dim arrResult As Variant
    Dim i As Integer
    
    arrResult = Split(strInput, ", ")
    
    Dim numRows As Long
    numRows = UBound(arrResult) - LBound(arrResult) + 1
    
    Dim result() As Variant
    ReDim result(1 To numRows, 1 To 1)
    
    For i = LBound(arrResult) To UBound(arrResult)
        result(i + 1, 1) = arrResult(i)
    Next i
    
    SplitStringToArray = result

End Function
```
  Hopefully, you have found the idea helpful. Download the solution workbook. Stay blessed.
  
  DOWNLOAD SOLUTION WORKBOOK
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy
Olaide Apr 30, 2024 at 1:47 AM

Thanks a lot for these codes. They worked pretty well but on a few set of coordinates.

I have over 5,000 sets of coordinates and am wondering if there is anything that can reverse geocode these in minutes?

Regards,
Olaide
- Reply
  Lutfor Rahman Shimanto May 2, 2024 at 12:28 PM
  Hello Olaide
  
  Thanks for thanks! You are very welcome. Your appreciation means a lot to us.
  
  You are right! The user-defined function works perfectly fine for a few sets of coordinates. I have reviewed the code and estimated the time required to perform it on a pair of coordinates; it takes 5.7 seconds on my machine (the time can vary from device). It will take almost 8 hours to perform 5000 sets of coordinates with a spontaneous internet connection.
  
  The user-defined function mentioned in this article uses the OpenStreetMap Nominatim API to perform reverse geocoding. It retrieves location information based on the lat and long values given to it. It must take the required time to perform perfectly.
  
  So, it is impossible to reverse geocode 5000 sets of coordinates within minutes. If you ever feel like getting location information without applying the user-defined function like the regular Excel function, you can use the following sub-procedure. You need to run the code, and it will consider the lat and long values as columns B and C starting from row 3 and display the result in column D.
```
Sub PopulateReverseGeocoding()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    For i = 3 To lastRow
    
        Dim lat As Double
        Dim lon As Double
        lat = ws.Cells(i, "B").Value
        lon = ws.Cells(i, "C").Value
        
        ws.Cells(i, "D").Value = ReverseGeocoder(lat, lon)
    
    Next i

End Sub

Function ReverseGeocoder(lati As Double, longi 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(lati) + _
          "&lon=" + CStr(longi)
    
    xD.Load (URL)
    
    If xD.parseError.ErrorCode <> 0 Then
        ReverseGeocoder = xD.parseError.reason
    Else
        xD.SetProperty "SelectionLanguage", "XPath"
        Dim loca As MSXML2.IXMLDOMElement
        Set loca = xD.SelectSingleNode("/reversegeocode/result")
        
        If loca Is Nothing Then
            ReverseGeocoder = "No location found."
        Else
            ReverseGeocoder = loca.Text
        End If
    End If
    
    Exit Function
    
ErrorHandler:
    ReverseGeocoder = "Error occurred: " & Err.Description
    
End Function
```
  I hope you understand the situation. Stay blessed.
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy
Jen May 11, 2024 at 3:29 PM

I’m glad I came back here! it didn’t work for me before but I see that you updated it. I’m going to give it a shot! fingers crossed.
- Reply
  Shamima Sultana May 12, 2024 at 8:32 AM
  
  Hello Jen,
  
  Thanks for coming back, hopefully it will work this time. If it doesn’t work, you can let us know your problem in the comment section below.
  
  Regards
  ExcelDemy

Jen May 16, 2024 at 11:39 AM

SO YAYYY IT WORKS BUT… Can I get the code with less information and would that require less RAM to perform? Like I dont need the county or the town… Just the physical address like house number, road, city, state and zip code? Thanks again!

Lutfor Rahman Shimanto May 16, 2024 at 1:23 PM

Dear Jen

Thanks for explaining your requirements clearly. I have reviewed the existing user-defined function and tried to improve it to achieve your goal. The improved user-defined function will try to populate house numbers, roads, cities, states and zip codes by taking Lat and Long values; if any item is missing, it will display an extension like Incomplete!