How to Convert Latitude and Longitude to Address in Excel

Consider a List of Coordinates, which includes the Latitude and Longitude in columns B and C, respectively. We’ll convert these coordinates into human-readable addresses.

Method 1 – Applying VBA Code for a User-Defined Function

Step 1 – Open the Microsoft Visual Basic for Applications IDE 

• Go to the Developer tab.
• Select Visual Basic in the Code group of commands.

• The Microsoft Visual Basic for Applications window pops up.
• Go to the Insert tab.
• Select Module from the available options.

• This will add a code module on the right side of the display.

Step 2 – Select the Right Reference 

• Go to the Tools tab.
• Click on References… from the options.

• The References-VBAProject dialog box opens.
• In the Available References section, check the box for Microsoft XML, v3.0.
• Click OK.

Step 3 – Build the Script

• Copy the following code and paste it into the module.

Option Explicit
Function ReverseGeocode(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
ReverseGeocode = 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
ReverseGeocode = xD.XML
Else
Application.Caller.Font.ColorIndex = vbOK
ReverseGeocode = loca.Text
End If
End If
Exit Function
0:
Debug.Print Err.Description
End Function

• Save the workbook as an Excel Macro-Enabled Workbook.

Step 4 – Use the User-Defined Function 

• Construct a new column named Address under Column D.

• Select cell D5 and insert the following in the cell.

• Select the ReverseGeocode function. Double-click on it or tap the Tab key to put it on the worksheet.

• Provide the arguments of the function and press Enter.

• The first address is converted from the latitude and longitude in cells B5 and C5 and put in cell D5.

• Bring the cursor to the bottom-right corner of cell D5 and it’ll look like a plus (+) sign. That’s the Fill Handle tool.
• Double-click on it to copy the formula up to cell D14.

• All the longitudes and latitudes have been converted to addresses.

• You can compare the result with the following screenshot. It’s a picture of a website from where we get the latitudes and longitudes.

Method 2 – Using an Online Geocoding Tool

Steps:

• Go to Geoapify.com

• We removed the heading from the previous dataset.
• Save the file.

• Scroll down in your browser and you will get a place to input your dataset file.
• Click on the “Browse Files” button.

• In the Open window, find the Excel file.
• Click on Open.

• We can see our file is inserted, and the website is showing a short preview of our dataset.

• Check the boxes for Latitude and Longitude which are our column names.
• Choose lat for Latitude and lon for Longitude.

• We selected English in the Language box.
• Click on the Geocode button.

• Click on Download geocoding results.

• This will save a CSV file on the PC. Open the file to see the result.

Note: We’ve added some formatting to the CSV file for visual beautification. You can go through the article Formatting CSV File in Excel to learn more.

Download the Practice Workbook

<< Go Back to Geocoding in Excel | Learn Excel