Stack Exchange Network
Stack Exchange network consists of 181 Q&A communities including
Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Visit Stack Exchange
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
Anybody can ask a question
The best answers are voted up and rise to the top
8 years, 11 months ago
I have a spreadsheet in Google Sheets with about 20-30 rows, with the following 6 columns:
where the last two columns are empty.
What is an easy way, for a novice user who is a non-programmer, to geocode the address and populate the Latitude and Longitude fields with result of geocoding?
I just need to populate the Latitude & Longitude fields. I do not need to see any data on a map.
Jun 23, 2014 at 5:41
Devdatta Tengshe Devdatta Tengshe
40.8k 33 33 gold badges 135 135 silver badges 253 253 bronze badges
this Google Sheets formula in an answer in the Google Forums useful -
Replace "C1" in the formula, with the cell that holds the address to geocode
Nov 3, 2017 at 5:30
171 1 1 silver badge 3 3 bronze badges
GPS Visualizer is a handy site which performs batch geocoding.
To use this free utility, simply enter addresses in the box to the left, one per line, and click "Start geocoding" to find their latitude and longitude.
Note that "Google's policies do not allow output in text format" so you'll need to use another geocoder like Bing or MapQuest.
(Alternatively, and assuming this doesn't break the ToS, you can use Google as the geocoding engine, then download the results as a KMZ and extract the coordinates from this.)
Jun 23, 2014 at 6:11
Stephen Lead Stephen Lead
20.9k 16 16 gold badges 109 109 silver badges 228 228 bronze badges
Install the geocode plugin avasilable in the Google Sheets add-ons. It's a VBA excel macro, but it will help you if you are not clued up enough with coding.
Then in your main toolbar you should see the Geocode option. CLick it and you have 2 options: from address to lon,lat and other way round.
Remember, that script can do only 1000 records per day! Otherwise you won't be able to carry on, even with your own macro!
Oct 30, 2019 at 10:07
3,771 1 1 gold badge 22 22 silver badges 68 68 bronze badges
Openstreetmap (OSM) API can help finding lat,lon information without any subscription/ API key requirement.
B1 = Address e.g 1600 Pennsylvania Avenue NW, Washington, DC 20500
Replace space with "%20" in address
Create search link with response in xml format:
Get lat and lon data in XML format and parse it with XPath filters.
Get Latitude, Longitude of given address:
Dec 23, 2020 at 19:17
You can geocode a spreadsheet using
Geocodio. You can upload the spreadsheet or just copy/paste the list of addresses, and it'll turn the addresses into latitude/longitude it for you. The first 2,500 addresses per day are free.
Disclosure: I'm a co-founder of Geoocodio.
64.7k 28 28 gold badges 105 105 silver badges 327 327 bronze badges
Nov 20, 2017 at 20:03
19 2 2 bronze badges
For non programmers I would recommend the following approach:
Convert Address to Lat Long tool (CSV2GEO) that is designed to perform batch geocoding by non programmers.
Here are the steps:
Create your Google Sheet
Then go File-Download->Excel or CSV
Load the CSV2GEO tool and upload the file by either dragging it or simply browse from your local directories.
Click on the header of column C , where the address is stored and select the appropriate address tokens. In the example select all.
Click "Process Data" and the system will process the first ten rows and display them for you. Check if the addresses appear ok. You can also download into a file the first ten for detailed comparison. If all good, click "Get All Data". Somewhere along the way if you are not registered or logged in, the system will ask you to do that.
And of course, the tool provide daily free geocoding of hundred addresses.
Disclosure: I am the founder of CSV2GEO.
Mar 5, 2021 at 13:18
56 4 4 bronze badges
Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. Answers without enough detail may be edited or deleted.
Use free Google Sheets Add-on
Watch it populate in columns Lat & Lon
Nov 13, 2015 at 15:39
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our
Accept all cookies
Necessary cookies only