1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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)

Discussion in 'Ask an Excel Question' started by bvanscoy678, Apr 18, 2013.

  1. bvanscoy678

    bvanscoy678 Member

    Messages:
    60
    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
  2. bvanscoy678

    bvanscoy678 Member

    Messages:
    60
    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
  3. Faseeh

    Faseeh Excel Ninja

    Messages:
    2,663
    bvanscoy678,


    Can you post some sample of data.


    Faseeh
  4. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    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!
  5. bvanscoy678

    bvanscoy678 Member

    Messages:
    60
    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
  6. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    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!
  7. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,603
    I'd suggest reading this:

    http://policeanalyst.com/using-the-google-geocoding-api-in-excel/
  8. DaveTurton

    DaveTurton New Member

    Messages:
    261
    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 (vb):
    '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]
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,603
    How timely


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


    http://newtonexcelbach.wordpress.com/2013/04/19/geodesix/
  10. DaveTurton

    DaveTurton New Member

    Messages:
    261
    That's impresive
  11. imgonna

    imgonna New Member

    Messages:
    45
    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
    mfuser likes this.
  12. mfuser

    mfuser New Member

    Messages:
    1
    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

Share This Page