This online batch geocoder retrieves latitude and longitude of multiple adresses. It is based on OpenStreetMap API. Note that if a city name is misspelled or the address does not match the underlying OSM database, then OSM does not return geocodes, as it has no spelling correction/closest match algorithm.
There are many geocoding service providers. Below we provide Excel interfaces to three different providers. They all offer spelling correction, hence the "hit rate" becomes almost 100% (whereas OSM's "hit rate" can be considerably lower).
Download Excel Batch Geocoder - Google Maps
(50kB - downloaded 655 times, developed by Stelling Consulting, 2022‑02‑16, use at own risk). You will need to create your own Google Maps key (not that easily done, compared to other providers below) to run it. Free to use, up to some amount. After that, you will need to pay Google Maps.
Download Excel Batch Geocoder - TomTom
(53kB - downloaded 565 times, developed by Stelling Consulting, 2022‑02‑18, use at own risk). You will need to create your own TomTom key (easily done) to run it. Free to use, up to some amount. After that, you will need to pay TomTom which is less expensive than Google Maps or some other geocoding providers.
Download Excel Batch Geocoder - BING (developed by a 3rd party, use at own risk). You will need to create your own Microsoft Bing key (easily done) to run it. If you qualify for free use, then the Basic key offers limited free use, else you need to setup an Enterprise key.
Download geocoding files (1 zip file for each country, and 1 zip file for whole world) from www.geonames.org, import data into Excel, and XLOOKUP latitudes/longitudes.
If you do not want spend much time on geocoding, then you can use the online Batch Geocoder (developed by Stelling Consulting): based on Google Maps API. It offers spelling correction, hence the "hit rate" becomes almost 100%. Google Maps API works superior. Years ago Google offered their service for free but nowadays they charge for it, so a fee is applicable for using the superfast Batch Geocoder.
Geocode
Kill run
Save results to txt file
READY
To save results manually: go to the output text area and select all data (CTRL-A), then copy (CTRL-C) and paste (CTRL-V) into e.g. your MS Excel file.
To geocode another batch: refresh page (F5) after saving your results first!
Check postal codes for leading zeroes
If - for example - you notice German or French postal codes with only 4 digits only in your data instead of 5, then there is a leading zero issue/missing.
This issue is quickly caused in/by Excel if you have not set your postal code column to type "text". It then may cause geocoding problems.
YOUR INPUT - semicolon separated or tab separated Record_number;City;Postal_code;Street_name_and_number;Country *
* You may specify country and postal code only, or leave out street number. But the more correct address data provided, the more accurate the geocodes will be.
