Excel Mac 2016 Get Longitude Latitude Coordinates For An Address

Posted : admin | On 15-09-2021

See how to calculate distance in Excel, using Latitude and Longitude, in this tutorial by Excel MVP, Jerry Latham.

Jun 15, 2020 Some apps use decimal format (48.85833) while others return the coordinates in degrees, minutes and seconds (48°51'29.99'). In this article I will show you how to convert from one format to the other and vice-versa. Add GPS coordinates to address. If you are looking to add GPS coordinates to your address, please go to this page. Say that you have a list of cities in Excel and need to know the latitude and longitude for each city. A new Geography Data Type feature coming to Office 365 will make this easy. Check the Data tab in the Excel ribbon. Do you have a new Data Type category with Stocks and Geography? If you have the new features, read on. Bing Maps Api Get Latitude Longitude From Address.

Getting Started

For those who are in a rush for the solution and don't need all the background information, jump to the longitude latitude code. Or download the calculation workbook, and enter your longitude and latitude.

The search for an accurate solution to this problem has led me to numerous sites and attempted solutions. A long list of related sites is included at the end of all of this, but the most crucial to what I've found to be the current end of the road are these:

Bing Maps Api Get Latitude Longitude From Address. I am using Bing Map and there are several pushpins in the map.

along with

Accuracy is Important

A formula that is accepted to provide results that are accurate to within millimeters is known as Vincenty's formula. Naturally the accuracy of the results depends in large part on the accuracy of the latitude/longitude pairs describing the two points.

Why all the fuss over accuracy? Well, from what I've seen of other formulas, especially those written as a single worksheet function, their values differ quite a bit for what might be considered 'life critical' situations. Typically they are short by some number of meters, typically about 20 to 30 feet per statute mile, and after flying just 30 or 40 miles, I wouldn't care to land several hundred feet short of the approach end of a runway, much less be off by over 7 miles on a trip between Los Angeles and Honolulu.

Since the general tendency in dealing with these types of calculations is to 'measure it with a micrometer, mark it with chalk and cut it with an axe', what we have here is a very precise micrometer to begin the measuring process with.

Excel Formulas for Distance

There are numerous Excel worksheet functions that will return an initial heading from one point to the destination point for a Great Circle path between them and similar formulas to return the distance between them.

But based on experience using them and comparing them to known measured distances, the Vincenty method of calculating the distance between the points has not been translated into a single Excel worksheet function, and very likely cannot be at least not easily.

Because it relies on reiterative calculations to deal with points that are very close to being on the exact opposite sides of the world, implementing it as even a series of Excel worksheet formulas is a daunting task.

Creating Excel Code for Distance

The basis for the solution presented by T. Vincenty can be found here:

During my search I first found the movable-type.co.uk page which presented the coded solution in JavaScript. That didn't do me much good as far as coming up with an Excel VBA solution; so I continued the search and finally found the lost-species.livejournal.com page!!

I thought the search was over. Almost, but not quite.

When I moved that code into an Excel code module I was confronted with two sections that Excel not so politely informed me were 'too complex' for it to evaluate. I managed to break those down into simpler statements that Excel could deal with and that did not corrupt the final results. That is the sum total of my contribution to the function provided below. I claim nothing more than that small contribution.

Working With Angles in Excel

But before using the function, there are a few preliminary steps that must be considered. Most significant is that Excel and VB works with angles expressed in radians, not as decimal values of the angles, nor from their initial 'plain English' representation.

Consider this situation:

You have a Latitude represented as 10° 27' 36' S (10 degrees, 27 minutes 36 seconds South)

You need to get that into radians, and there is not a direct way to do it, before we can get it to radians it has to be converted into a decimal representation. We need to see it as: 10.46 which is the decimal equivalent to 10° 27' 36' and we need to take into consideration whether it is a North or South latitude, with South latitudes being treated as negative numbers.

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address In The Philippines

Luckily Microsoft provides a couple of handy functions to convert standard angular notations to their decimal equivalent, and back, at this page:

Those routines are included at the code section and one of them has a change made by me to permit you to make a regular angle entry as

10~ 27' 36' S instead of 10° 27' 36' S

because ~ is directly accessible from the keyboard, while the ° is not.

Converting to Radians

After converting the standard notation to a decimal value, we still have to convert that to radians and deal with the sign of the radian result.

The routines and formulas here consider negative latitudes to be South latitudes and negative longitudes to be West longitudes. While this may seem unfair to those of us living in the western hemisphere, what can I say other than deal with it!?

A decimal degree value can be converted to radians in several ways in Excel and for this process, a simple function is used that is also included in the code presented later.

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address

The basic formula is

radians = angleAsDecimal x (Pi / 180)

where Pi is 3.14159265358979

The Final Solution

Excel mac 2016 get longitude latitude coordinates for an address in india

Copy all of the code below and paste it into a regular code module in your workbook. Instructions for placing the code into a regular module are here: Copy Excel VBA Code to a Regular Module

Set up your worksheet to pass the latitudes and longitudes of the start and end points as standard entries, then enter a formula to pass them to function distVincenty().

Given two points with these coordinates:

Point 1:

  • Latitude: 37° 57' 3.7203' S
  • Longitude: 144° 25' 29.5244' E

Point 2:

  • Latitude: 37° 39' 10.1561' S
  • Longitude: 143° 55' 35.3839' E

The general format of the function call is:

=distVincenty( Pt1_LatAsDecimal, Pt1_LongAsDecimal, Pt2_LatAsDecimal, Pt2_LongAsDecimal)

A raw formula would look like this [Note the double-double quotes after the seconds entries]. The SignIt() function, provided as part of the code, converts a standard angular entry to signed decimal value.

=distVincenty(SignIt('37° 57' 3.7203' S '), SignIt('144° 25' 29.5244' E'), SignIt('37° 39' 10.1561' S'), SignIt('143° 55' 35.3839' E'))

You can use the ~ symbol instead of the ° symbol if it makes it easier for you:

=distVincenty(SignIt('37~ 57' 3.7203' S '), SignIt('144~ 25' 29.5244' E'), SignIt('37~ 39' 10.1561' S'), SignIt('143~ 55' 35.3839' E'))

If the coordinates for Point 1 are in B2 and C2 and the coordinates for Point 2 are in B3 and C3, then it could be entered as

=distVincenty(SignIt(B2), SignIt(C2), SignIt(B3), SignIt(C3))

The result for the 2 sample points used above should be 54972.271, and this result is in Meters.

Problems Encountered

The initial code that I began working with generated 'formula too complex' errors in two statements. These statements were initially broken into two pieces and then those two separate calculations were 'rejoined' in a formula to obtain their final result without the 'formula too complex' error.

During the preparation of this document, the package was tested in Excel 2010 64-bit and began returning #VALUE! errors for all input values. Investigation determined that a portion of the already split formula to determine the deltaSigma value was generating an overflow error. This error did not occur in the 32-bit version of Excel 2010, nor in Excel 2003 (a 32-bit application).

The offending line of code was once again broken down into smaller pieces that were eventually rejoined in a mathematically correct process that resulted in the proper values being determined without resorting to any alteration of the original algorithm at all.

These sections are noted in the comments in the code for Function distVincenty() below.

For any questions regarding all of this, contact Jerry Latham at HelpFrom@JLathamSite.com.

The Longitude Latitude Code

Now, at last, the code:

Download the Workbook

To see the code, and test the formulas, download the Excel Distance Calculation sample workbook. The file is in xlsm format, and is zipped. There are macros in the workbook, so enable the macros if you want to test the code.

Excel Function Tutorials

This help guide will show you how to take a list of addresses and get latitude & longitude coordinates.

To begin, you will need your addresses (street address with city and country; just a city) in CSV or Excel format with one address per row. The address may be in one column or across multiple columns (e.g.: a column each for street, city, and country). Then consult the criteria below to find where you fit:

  • I have a spreadsheet with less than 5,000 locations and each street address is split up across multiple cells
    • GPS Visualizer (online)
    • QGIS (desktop)

GPS Visualizer

You will need one of the following free API keys: Bing Maps API key (geocoding limit unclear), Google Maps API key (2,500 free requests per day), or MapQuest API key (15,000 per month)

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Location

  1. Ensure that your spreadsheet has the following column headers and is saved as a CSV:
    • Header: name; content: name of the point (e.g.: business name, hospital, person who lives there, etc.)
    • Header: address; content: street address (e.g.: 1 Main Street)
    • Header: city; content: city
    • Header: state or province; content: state or province spelled out (e.g.: Ontario instead of ON)
    • Header: country; content: country spelled out
  2. Open your CSV in Notepad (Windows) or TextEdit (Mac), so there are no columns and rows but comma-separated data
  3. Go to http://www.gpsvisualizer.com/geocoder/
  4. Copy your data from Notepad/TextEdit into the Input textbox (we recommend that you do this in batches instead of 1000 at once)
  5. In the Source dropdown, select which API key you have
  6. Copy that API key into the API key textbox below
  7. Click Start Geocoding
  8. Wait...it takes about one second per location
  9. Once the geocoding is complete, copy the contents of the Results as text box
  10. Paste the contents into a new Notepad/TextEdit document
  11. Save that document as a CSV (you may need to manually change the file extension when you save)
  12. Open the CSV in Excel to view and check your data in spreadsheet format. There will probably be errors, which is normal.
  13. You can now import that CSV into ArcMap (Add XY data), ArcGIS Pro (Add XY data), or QGIS (Add delimited text layer) and display the points

QGIS

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address In Nyc

QGIS is a GIS desktop software that is free and open source. It is downloadable from https://qgis.org/en/site/ and we recommend downloading the long term release (most stable) version.

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Location

Excellent geocoding instructions from our colleagues at uOttawa are found here: https://ssimpkin.github.io/dhsite2018/geocoding-spreadsheets/

Address

GIS Lounge also provides similar instructions: https://www.gislounge.com/how-to-geocode-addresses-using-qgis/

Excel Mac 2016 Get Longitude Latitude Coordinates For An Address In China

Remember: you can always contact us at gis@carleton.ca.