# 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:
`=B5.Latitude`
• Hit Enter.

• Use the AutoFill tool down.

• In D5, apply the following:
`=B5.Longitude`

• 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:
`=Co_Ordinates(B5)`

• Press Enter.
• Use the AutoFill tool.

<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio