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

In this article, we will learn how to convert an address to latitude and longitude in Microsoft Excel. In detail, Excel provides some new cool features to extract coordinates of any location in a minute. So, to summarize, let’s see these 2 easy methods to convert Address to Lat-Long in Excel for your articles and workbooks.

Convert Address to Lat Long in Excel: 2 Easy Methods

We’ll use a new Excel data type, Geography introduced in Microsoft Excel 365 and Excel VBA code in these 2 methods. To demonstrate, we take a dataset where column B consists of various addresses. And, column C refers to the latitude-longitude of those locations. For better understanding, we use the degrees & decimal minutes system to indicate the coordinates. In this system, we will assign positive & negative values to the coordinates. For instance, a positive latitude indicates it falls north of the equator whereas a negative latitude locates south. Similarly, positive & negative longitude indicate the east & west of the Prime meridian respectively. Meanwhile, for this purpose of demonstration, I will use Microsoft Excel 365. I used the sample dataset listed below.

convert address to lat long excel

1. Transform Address to Lat Long in Excel Through Geography Data Type

Firstly, this method will show you how to convert an address to its latitude and longitude using a new data type. As shown below, we have a list of address data from different cities all over the world. Provided that, we will use the Geography feature to illustrate this method. The Geography data type converts the city name data into the Geography data type. Geography data type gives out details like population, area, languages, and many more as long as you are connected to the internet. Moreover, this in-built feature provides the latitude and longitude of a specific area. Let’s follow the procedure.


  • First, Select column B5:B12.
  • Then, press the Data tab.
  • Next, tap Geography as marked below.

Transform Address to Lat Long in Excel Through Geography Data Type

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

  • Now, in cell C5, type the following formula:
  • Also, press enter to get the output.

  • Next, use the AutoFill tool.
  • Hence, you get the latitudes.

  • Similarly, we will try to extract the longitude also.
  • In this case, type this formula:

  • Finally, press enter to get the result.
  • But, this output will return the column as a formula cell range. We will need these columns as latitude & longitude datatypes. So, we need to follow one or two steps more.
  • First, select columns C & D.
  • Then, press Copy in the Home tab.

  • Now, we need to Paste them into the dataset as Values & Number Formatting (A).
  • Click the Dropdown icon under Paste.
  • And finally, paste it as Values & Number Formatting (A) in the Paste Values.
  • Hence, the desired result appears.

address in lat long through Geography data type in Excel

2. Embed VBA Code to Convert Address to Lat Long in Excel

Secondly, in this method, we will try to extract the latitude & longitude of various addresses using Excel VBA Code. Follow these easy processes to do so.


  • Firstly, go to the Developer tab.
  • After that, click the Visual Basic window.

Embed VBA Code to Convert Address to Lat Long in Excel

  • Eventually, a console will appear.
  • Here, tap Insert and then consecutively press Module.
  • As a result, a module will appear.

  • Before going into the code section, we need to do a critical task.
  • Go to Tools and then select References as shown below.

  • After a while, the References box will appear.
  • Here, scroll down till Microsoft XML, v3.0 appears.
  • Now, check the box, and at last press OK.

  • Next, in this part, copy the Excel VBA Code and paste it into the Module.
  • You can copy the code given below:
Function Co_Ordinates(address As String) As String
    Application.Caller.Font.ColorIndex = xlNone
    Dim xDoc As New MSXML2.DOMDocument
    xDoc.async = False
    xDoc.Load ("" + WorksheetFunction.EncodeURL(address))
    If xDoc.parseError.ErrorCode <> 0 Then
        Application.Caller.Font.ColorIndex = vbErr
        Co_Ordinates = xDoc.parseError.reason
        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
            Application.Caller.Font.ColorIndex = vbOK
            Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
        End If
    End If
End Function

  • However, we need to save this code as Excel Macro-Enabled Workbook type.
  • Subsequently, press the Save icon.

  • Eventually, it will return a Save As console.
  • Afterward, open the Save as type bar to explore the types.

  • Finally, select the Excel Macro-Enabled Workbook type and save it.

  • After saving the code file, close the tab.
  • Next, go to your dataset and select cell C5.
  • Here, we type another formula:

  • Lastly, press Enter.
  • Use the AutoFill tool.
  • Hence, the required latitudes & longitudes will pop up.

output of address in lat long through VBA

Download Practice Workbook

You can download this workbook to practice yourself.


In conclusion, we have discussed here some easy ways to create an Excel zip code formula. Please feel free to leave any further queries or recommendations in the comment box below.

<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan
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

  1. Hello,
    My version of Excel does not have a “developer” tab. Is there another way to do this?

Leave a reply

Advanced Excel Exercises with Solutions PDF