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

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.

• To get the longitude, enter the following formula in D5.

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

Read More: Convert Degrees Decimal Minutes to Decimal Degrees

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.

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