Shahriar Abrar Rafid
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.
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.
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.
Instantly, it will add a code module at the right side on the display.
Step 02: Select Right ReferenceÂ
- Now, advance to the Tools tab.
- Then, click on References… from the options.
Suddenly, the References-VBAProject dialog box opens.
- In the Available References section, check the box of Microsoft XML, v3.0.
- As usual, click OK.
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
- Following this, Save the workbook till this condition.
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.
- 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.
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.
Lastly, all the longitudes and latitudes have been converted to addresses in our Excel worksheet.
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.
- 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.
- Primarily, scroll down a bit and you will get a place to input your dataset file.
- Here, click on the Browse Files button.
- 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.
- 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.
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.
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.
