• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Excel to find Lat and Long from a street address (geocode)

bvanscoy678

bvanscoy678

Member
Hello,


After reading Chandoo's posting on "Finding a Zip Code" with the new formulas in Excel 2013, that got me thinking. I have a list of several hundred addresses that I would like to find the lat and long for each one. Can this be done?


Thanks,

Brent
 
I was able to find a website called Mapcite.com. Not sure if this is what I am looking for, but I'll give it a test. Thanks
 
Hi, bvanscoy678!

If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

http://chandoo.org/forums/topic/find-latitude-and-longitude-of-any-address-using-google-map-api-and-vba

Regards!
 
SirJB7,


Yes, you are correct. I should have searched first. The idea popped in my head as I was reading Chandoo's post. Thank you for the link.


Brent
 
Hi, bvanscoy678!

Glad to help. Thanks for your kind words and welcome back whenever needed or wanted.

And of course, just advise if any issue.

Regards!
 
I'd suggest reading this:

http://policeanalyst.com/using-the-google-geocoding-api-in-excel/
 
Here are two UDFs one for Latitude and one for Lonitude. You can use them in a macro or as a formula


i.e. lat(A1) will return the latitude of the place/postal code in A1

[pre]
Code: 
'finds the longitude of a postcode
'max 650 queries per 24 hours

Function lon(z_t As String)
Dim sURL As String
Dim BodyTxt As String
Dim apan As String, lo_g As String
Dim oXH As Object

'create web url
sURL = "http://maps.googleapis.com/maps/api/geocode/xml?address="
sURL = sURL & Replace(z_t, " ", "+") & ",+" & _
"&sensor=false"
' browse url
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", sURL, False
.Send
BodyTxt = .responseText
End With
apan = Application.WorksheetFunction.Trim(BodyTxt)

'Longitude
apan = Right(apan, Len(apan) - InStr(1, apan, "<lng>") - 4)
lo_g = Left(apan, InStr(1, apan, "</lng>") - 1)
lon = lo_g

End Function

'finds the latitude of a postcode
'max 650 queries per 24 hours

Function lat(z_t As String)
Dim sURL As String
Dim BodyTxt As String
Dim apan As String, la_t As String
Dim oXH As Object
'create web url

sURL = "http://maps.googleapis.com/maps/api/geocode/xml?address="
sURL = sURL & Replace(z_t, " ", "+") & ",+" & _
"&sensor=false"
' browse url
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", sURL, False
.Send
BodyTxt = .responseText
End With
apan = Application.WorksheetFunction.Trim(BodyTxt)
'Latitude
apan = Right(apan, Len(apan) - InStr(1, apan, "<lat>") - 4)
la_t = Left(apan, InStr(1, apan, "</lat>") - 1)

lat = la_t

End Function
[/pre]
 
How timely


Doug over at http://newtonexcelbach.wordpress.com posted this today:


http://newtonexcelbach.wordpress.com/2013/04/19/geodesix/
 
In Excel 2013 this can be achieved with a combination of:


A1=WEBSERVICE("http://maps.googleapis.com/maps/api/geocode/xml?address=POSTCODEHERE+,+&sensor=false") > replace POSTCODEHERE with your post code / zip


To extract the latitude


B1=FILTERXML(A1,"//result/geometry/location/lat")


To extract to longitude


B1=FILTERXML(A1,"//result/geometry/location/lng")


Some of the nice(r) features of Excel 2013

Cheers

G
 
imgonna said:
In Excel 2013 this can be achieved with a combination of:


A1=WEBSERVICE("http://maps.googleapis.com/maps/api/geocode/xml?address=POSTCODEHERE+,+&sensor=false") > replace POSTCODEHERE with your post code / zip


To extract the latitude


B1=FILTERXML(A1,"//result/geometry/location/lat")


To extract to longitude


B1=FILTERXML(A1,"//result/geometry/location/lng")


Some of the nice(r) features of Excel 2013

Cheers

G
Click to expand...
Request you to help me with FILTERXML data.
I have mapped one RSS to an excel file. While all other data comes clean in various cells, one cell gets a piece of HTML/XML. It looks like this -

table border="0" cellspacing="4" cellpadding="2"> <tr> <td style='background-color:#CADDEC;FONT-FAMILY: Verdana,Arial,San-serif; FONT-SIZE: 8pt; FONT-WEIGHT: bold; Color:#012540;'><b>AUM Month </b></td> <td style='FONT-FAMILY: Verdana,Arial,San-serif; FONT-SIZE: 8pt; Color:#012540;'> Jul-2014 </td></tr><tr> <td style='background-color:#CADDEC;FONT-FAMILY: Verdana,Arial,San-serif; FONT-SIZE: 8pt; FONT-WEIGHT: bold; Color:#012540;'><b>Average AUM Excluding Fund of Funds</b></td> <td style='FONT-FAMILY: Verdana,Arial,San-serif; FONT-SIZE: 8pt; Color:#012540;'> 2250809.98 </td></tr><tr> <td style='background-color:#CADDEC;FONT-FAMILY: Verdana,Arial,San-serif; FONT-SIZE: 8pt; FONT-WEIGHT: bold; Color:#012540;'><b>Average AUM Fund of Funds </b></td> <td style='FONT-FAMILY: Verdana,Arial,San-serif; FONT-SIZE: 8pt; Color:#012540;'> 10715.85 </td></tr></table>

Now, I need your help with the following questions?
1- Does this qualify as XML for the FILTERXML to work?
2- If yes, please let me know the formula /xpath to filter
AUM Month, Average AUM Excluding Fund of Funds and Average AUM Fund of Fund.
3- If this is not possible by filterxml, do you have any other solution?
Thanks
 
You must log in or register to reply here.
Back
Top