Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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 LatLong in Excel for your articles and workbooks.


Download Practice Workbook

You can download this workbook to practice yourself.


2 Easy Methods to Convert Address to Lat Long in Excel

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.

Steps:

  • 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:
=B5.Latitude
  • 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:
=B5.Longitude

  • 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

Read More: How to Create Latitude Longitude Converter in Excel (2 Examples)


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.

Steps:

  • 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 appear.
  • 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 ("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

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

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

output of address in lat long through VBA

Read More: How to Convert Latitude and Longitude to Address in Excel


Conclusion

In conclusion, we have discussed here some easy ways to create an Excel zip code formula. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please feel free to leave any further queries or recommendations in the comment box below.


Related Articles

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo