How to Use Geocoding in Excel? – 2 Examples

Geocoding is the process of converting addresses or place names into geographic coordinates. Geographic coordinates are latitude and longitude.

Download Practice Workbook

What Do You Mean by Geocoding in Excel?

Geocoding in Excel is the process of converting an address into geographic coordinates.

The Latitude is the distance of a location from the equator. It determines the position of a location in the north or south direction.

The Longitude is how far east or west a location is from the Prime Meridian. It indicates its position in the east-west direction.

Example 1 -How to Geocode in Excel with Geography Data Type

• There are city names in column B5:B14.
• Select the column and go to the Data tab > Data types > Geography.

• Enter the following formula in C5 to get the latitude of the city.
`=B5.Latitude`

• To get the longitude, enter the following formula in D5.
`=B5.Longitude`

• Drag down the Fill Handle to see the result in the rest of the cells.

Note: Geography data type was introduced in Excel 365. If you are using an older conversion of Excel, you may not find this feature.

Example 2 – How to Use VBA for Geocoding in Excel

• Go to the Developer tab > Visual Basic.

• Choose Insert > Module.

• Select a reference in the tools panel. Go to Tools > References.

• The VBAProject dialog box opens >Â Select Microsoft XML, v3.0, and click OK.

• Enter the following code in the module :
``````Function Co_Ordinates(address As String) As String
Â Â Â  Application.Caller.Font.ColorIndex = xlNone
Â Â Â  Dim xDoc As New MSXML2.DOMDocument
Â Â Â  xDoc.async = False
Â Â Â  xDoc.Load ("https://nominatim.openstreetmap.org/search?format=xml&q=" + WorksheetFunction.EncodeURL(address))
Â Â Â  If xDoc.parseError.ErrorCode <> 0 Then
Â Â Â Â Â Â Â  Application.Caller.Font.ColorIndex = vbErr
Â Â Â Â Â Â Â  Co_Ordinates = xDoc.parseError.reason
Â  Â Â Else
Â Â Â Â Â Â Â  xDoc.SetProperty "SelectionLanguage", "XPath"
Â Â Â Â Â Â Â  Dim loc As MSXML2.IXMLDOMElement
Â Â Â Â Â Â Â  Set loc = xDoc.SelectSingleNode("/searchresults/place")
Â Â Â Â Â Â Â  If loc Is Nothing Then
Â Â Â Â Â Â Â Â Â Â Â  Application.Caller.Font.ColorIndex = vbErr
Â Â Â  Â Â Â Â Â Â Â Â NominatimGeocode = xDoc.XML
Â Â Â Â Â Â Â  Else
Â Â Â Â Â Â Â Â Â Â Â  Application.Caller.Font.ColorIndex = vbOK
Â Â Â Â Â Â Â Â Â Â Â  Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
Â Â Â Â Â Â Â  End If
Â Â Â  End If
End Function``````

• Save the code and close the module.
• Enter the following formula in C5. The formula will return the latitude and longitude of the address in B5. Copy the formula using the flash fill.
`=Co_Ordinates(B5)`

Read More: Perform Reverse Geocoding

Â Things to Remember

• Save your Excel file as XLSM if there are any VBA codes.

Frequently Asked Questions

1. Are there any functions available in Excel that geocode automatically?

Ans: There isnâ€™t any formula available for geocoding purposes.

2. How do I geocode from an address in Excel?

Ans: If you have a city name, you can use Excelâ€™s Geocode feature to find latitude and longitude. If you have a street address, it is better to use VBA.

3. How to add latitude and longitude in Excel?

Ans: You can manually add the latitude and longitude of different places in Excel or use the Geocode feature to extract latitude and longitude from a city address.

Geocoding in Excel: Knowledge Hub

<< Go Back toÂ Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Maruf Niaz

Md. Maruf Niaz, BSc, Textile Engineering, Bangladesh University of Textiles, has worked with the ExcelDemy project for 11 months. He works as an Excel and VBA Content Developer who provides easy solutions to Excel-related problems and regularly writes fantastic content articles. He has published almost 20 articles in ExcelDemy. As an Excel & VBA Content Developer for ExcelDemy, he offers solutions to complex issues. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF