Getting LAT and LONG from address

blackgolf · Mar 8, 2012

Hi

IS there a way to get the LAT (in decimal) and LONG (in decimal) of addresses within Excel 2010?

I have address in Column A and require the LAT in COlumn B and the LONG in Column C.

Examples of addresses;
[FONT=&quot]23 Vlam Cresent, NEWCASTLE [/FONT]
[FONT=&quot]40 Milner Street, Jacobs, DURBAN[/FONT]

The commas can be stripped if needed.

Thus Result should be;
[FONT=&quot]23 Vlam Cresent, NEWCASTLE Lat= -27.76257 Long= 29.95101
[FONT=&quot]40 Milner Street, Jacobs, DURBAN[/FONT] [/FONT] Lat= -29.92911 Long= 30.98408

Help will be much appreciated.
Chris

adam087 · Mar 8, 2012

I found this with a quick google search which may allow you to look up against a postcode (takes you to a different site, no commercial relationship with me, in fact I know nothing about them, use at your own risk).

http://www.freemaptools.com/download-uk-postcode-lat-lng.htm

Does that help?

Regards
Adam

blackgolf · Mar 8, 2012

Hi Adam

Thanks for the reply but it does not help. The site only covers the UK and you must provide a postal code.

I have a long list of addresses, like 100+ of which I need the LAT and LONG.
I have a nice website that gives me the LAT and LONG when I paste in the address, but it will take me forever to do them manually.

There must be code in Excel to send the address to a website and return the LAT and LONG in the columns next to it. Well I hope someone knows.

Not sure if we are allowed to paste websites here, but the website below is free and one just paste the address and a second later you get the lat and long. You can even see it on a map. Try your own address and see.

Just replace the @@@@ with http.
@@@@://universimmedia.pagesperso-orange.fr/geo/loc.htm

Regards

adam087 · Mar 8, 2012

Can't help then I'm sorry. Interfacing with the web like that is outside my skills

Sure someone else'll jump in though.

Regards
Adam

hiker95 · Mar 8, 2012

blackgolf,

Something like this?

Excel Workbook

A

B

C

1

Address

LAT

LONG

2

23 Vlam Cresent, NEWCASTLE Lat= -27.76257 Long= 29.95101

-27.76257

29.95101

3

40 Milner Street, Jacobs, DURBAN Lat= -29.92911 Long= 30.98408

-29.92911

30.98408

4

Sheet1

The formula in cell B2, copied down:

=MID(A2,(FIND("Lat= ",A2)+5),9)

The formula in cell C2, copied down:

=MID(A2,(FIND("Long= ",A2)+6),8)

blackgolf · Mar 8, 2012

Hi Hiker
Thanks for the reply, but problem is I do not have the LAT and LONG.

I only got the addresses, like below:

[FONT=&quot]23 Vlam Cresent, NEWCASTLE [/FONT]
[FONT=&quot]40 Milner Street, Jacobs, DURBAN[/FONT]

I now need the LAT and LONG for them from within Excel.

Thanks
Chris

hiker95 · Mar 8, 2012

blackgolf,

See:

How to get lat/long coordintates with VBA Excel
http://www.mapforums.com/how-get-lat-long-coordintates-vba-excel-7094.html

Resources for determining your latitude and longitude
http://jan.ucc.nau.edu/~cvm/latlon_find_location.html

Finding the location of a non-US city
Here are some sites that have the locations of major cities in the world.

http://www.realestate3d.com/gps/world-latlong.htm
http://www.infoplease.com/ipa/A0001769.html

blackgolf · Mar 8, 2012

Thanks a lot.

I have been to some of the sites you gave me but too complex.
I presume this is a study on it's own.

I found an excel file that call up yahoo to get the lat and long but for some reason I get nothing returned.

Will try further unless someone else here know how to solve my dilema.

Cheers
Chris

Norie · Mar 8, 2012

Chris

Do you have the URL of a site where you can enter the address and get the lat/long?

blackgolf · Mar 8, 2012

Hi Norie

I got a few URL's yes. But in the mean time I found an already built Excel spreadsheet with the code where you can add rows of addresses and with the click of a button you get all the LATs and LONGs.
I am over the moon with it.

Do you also want it then I can send you the download link?
Cheers and thanks for all the responses.
Chris

Getting LAT and LONG from address

blackgolf

Board Regular

Excel Facts

adam087

Well-known Member

blackgolf

Board Regular

adam087

Well-known Member

hiker95

Well-known Member

blackgolf

Board Regular

hiker95

Well-known Member

blackgolf

Board Regular

Norie

Well-known Member

blackgolf

Board Regular

Similar threads

Forum statistics

Share this page

Getting LAT and LONG from address

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock