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

Consider a dataset where the column B consists of various cities and columns C and D refer to the latitude and longitude of those locations. We’ll use the degrees and decimal minutes system to indicate the coordinates.

Method 1 – Transform an Address to Lat-Long in Excel Through the Geography Data Type in Excel 365

Steps:

• Select column B5:B12.
• Press the Data tab.
• Select Geography as marked below.

• The Geography tool will convert your data into geography data type, and each data point gets a Map icon as shown below.

• In cell C5, use the following formula:

• Hit Enter.

• Use the AutoFill tool down.

• In D5, apply the following:

• Hit Enter and apply AutoFill.
• Select columns C and D.
• Press Copy in the Home tab.

• Click the Dropdown icon under Paste.
• Paste the data as Values & Number Formatting (A) in the Paste Values.

Method 2 – Embed VBA Code to Convert an Address to Lat-Long in Excel

Steps:

• Go to the Developer tab.
• Click on Visual Basic.

• A console will appear.
• Click on Insert and select Module.
• A module will appear.

• Go to Tools and then select References.

• The References box will appear.
• Check Microsoft XML, v3.0 and press OK.

• Copy the code given below and paste it into 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

• Press the Save icon.

• It will return a Save As console.
• Open the Save as type bar.

• Select the Excel Macro-Enabled Workbook type and save the file.

• Close the tab.
• Go to your dataset and select cell C5.
• Insert this formula:

• Press Enter.
• Use the AutoFill tool.

Download the Practice Workbook

<< Go Back to Geocoding in Excel | Learn Excel