How to Convert Latitude and Longitude to Address in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you have the latitude and longitude of a particular place, you can find out the address of this place using Excel. OMG!! So cool, isn’t it? In this article, we’ll demonstrate 2 easy and quick methods to convert latitude and longitude to address in Excel. Moreover, the pictures are so beautiful that you can understand the whole matter just by looking at them. So, let’s go through the whole article to learn this and implement it in your work life.


Download Practice Workbook

You may download the following Excel workbooks for better understanding and practice yourself.


2 Methods to Convert Latitude and Longitude to Address in Excel

For ease of understanding, we are going to use a List of Coordinates. This dataset includes the Latitude and Longitude in columns B and C respectively.

convert latitude and longitude to address in excel

Now, we’ll convert these coordinates into human-readable addresses. To do this we’ll use 2 different approaches. So, let’s explore them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Applying VBA Code

In our first method, we’ll imply the VBA script to our worksheet. Using the VBA code, we’ll create a user-defined function first. Then, we’ll apply this function to convert the coordinates into addresses. So, let’s follow the procedure step-by-step.

Step 01: Open Microsoft Visual Basic for Applications IDE 

  • At the very beginning, go to the Developer tab.
  • Then, select Visual Basic on the Code group of commands.

Open Microsoft Visual Basic for Applications IDE

Immediately, the Microsoft Visual Basic for Applications window appears before us.

  • Here, proceed to the Insert tab.
  • After that, select Module from the available options.

Inserting Code Module

Instantly, it will add a code module at the right side on the display.

Module inserted to write code to convert latitude and longitude to address in excel

Step 02: Select Right Reference 

Select Right Reference

Suddenly, the References-VBAProject dialog box opens.

  • In the Available References section, check the box of Microsoft XML, v3.0.
  • As usual, click OK.

Selecting Right Reference to convert latitude and longitude to address in excel

Step 03: Build the Script

  • At this time, 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

Build the Script

  • Following this, Save the workbook till this condition.

Saving workbook to convert latitude and longitude to address in excel

Note: While saving, make sure to save it as Excel Macro-Enabled Workbook.

Step 04: Employ the User-Defined Function 

Currently, we’ll employ the code in our worksheet.

  • Firstly, construct a new column named Address under Column D.

Employ the User-Defined Function

  • Secondly, select cell D5 and start writing the following in the cell.
=re

Immediately, you can see the suggestions for functions.

  • From the suggestions, select the ReverseGeocode function which we’ve created a while ago. Double-click on it or tap the TAB key to employ it on the worksheet.

  • Then, give the arguments of the function and press ENTER.

Employing Function to convert latitude and longitude to address in excel

Finally, the first address converted from the latitude and longitude in cells B5 and C5 is in cell D5.

  • Presently, bring the cursor to the right-bottom corner of cell D5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
  • Now, double-click on it to copy the formula up to cell D14.

Using Fill Handle to convert latitude and longitude to address in excel

Lastly, all the longitudes and latitudes have been converted to addresses in our Excel worksheet.

Employing VBA Code to convert latitude and longitude to address in excel

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

Read More: How to Convert Address to Lat Long in Excel (2 Easy Methods)


2. Using Online Geocoding Tool

This approach is quite easy. You don’t have to create any macro or function on your own. You just have to import the coordinates to an online website Geoapify and it will do the rest. So, without further delay, let’s dive in!

📌 Steps:

  • At first, click on the above link of the website.

Geoapify website

  • Here, we have prepared the dataset. It’s the same dataset that we used in the previous method. But the difference is we removed the heading in this for the convenience of this tool.

Using Online Geocoding Tool

  • Primarily, scroll down a bit and you will get a place to input your dataset file.
  • Here, click on the Browse Files button.

Importing File

  • In the Open window, select Desktop and choose the desired file.
  • After that, click on Open.

We can see our file is inserted already. And the website is showing a short preview of our dataset.

Preview of Dataset

  • Again, scroll for a while.
  • Now, check the boxes of Latitude and Longitude which are our column names.
  • Following this, choose lat for Latitude and lon for Longitude.

  • Then, we selected English in the Language box.
  • Lastly, click on the Geocode button.

Selecting Language to convert latitude and longitude to address in excel

It’s showing that all the locations have been geocoded. That means, we can get addresses from those coordinates.

  • Next, click on Download geocoding results.

Suddenly, it’ll save a CSV file on our desktop.

  • So, open the file to see the result.

Using Online tool to convert latitude and longitude to address in excel

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 know more about this.

Read More: How to Create Latitude Longitude Converter in Excel (2 Examples)


Conclusion

This article explains how to convert latitude and longitude to address in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Related Articles

Shahriar Abrar Rafid

Shahriar Abrar Rafid

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo