Calculate the distance between 2 postcodes in miles

Jaffabfc

Board Regular
Joined
Jul 5, 2013
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi,

i am wanting to be able to calculate the distance between 2 postcodes in excel.

i have a list of around 3000 places in the UK and i am wanting to know which is the closest to were i currently am.

To do this i am wanting to be able to put a column on the end (e.g. column f) to work out how much it is from A to B, is this even possible?

I am hoping there is something simple that i can do even if it means linking google maps into it.

Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi,
I think you will want to get the Latitude and Longitude coordinates for each postal code, then it can be calculated by formula.
I googled it and found a few places you can get it for free.

The trigonometry is a bit beyond me for the formula, but it looks like this will be a good starting point: link i found

Hope this helps!


Please check out my excel youtube channel :) Excel 101
 
Last edited:
Upvote 0
Hi,
I think you will want to get the Latitude and Longitude coordinates for each postal code, then it can be calculated by formula.
I googled it and found a few places you can get it for free.

The trigonometry is a bit beyond me for the formula, but it looks like this will be a good starting point: link i found

Hope this helps!


Please check out my excel youtube channel :) Excel 101

if i do the longitude and latitude though wont that do the distance as a pigeon distance and not as in proper distance for people driving etc.

thanks
 
Upvote 0
What you are asking is quite substantial.

To start you off you are going to want to look at either the BING maps API or Google Maps API.

Make sure you read the licensing details so that you are sure you are using them within the terms of service.

You then might need an understanding of javascript/JSON and/or XML.
 
Last edited:
Upvote 0
There was some code from Kyle123 which seems to work which was posted in a different forum but I am sure Kyle123 won't object to me re-posting it here and also below is an excerpt from his post giving brief instructions.

I had originally posted a thread as to how to get this via the google API which is extremely simple, grabbing this and putting it into excel directly however breaches the terms of use.

Since then, I have discovered that Microsoft also offers a similar API, you do however need to sign up and get a key (here) and accept their terms of use.

I don't think posting the below contravenes anything, but if anyone feels otherwise let me know and I'll happily remove this.

In the below, you need to input your API key in the relevant place (IN RED)

You'll also need to set a reference to Microsoft XML v6.0

Distance is in Kilometers and comment out Open Explicit if you use it.

Code:
Function GetDistance(sPCode As String, ePcode As String) As Double
    Dim t As String
    Dim re As XMLHTTP


    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=[COLOR="#FF0000"]YOUR_MS_KEY[/COLOR]"
    Set re = New XMLHTTP

    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re
        s = Split(.responseText, "<TravelDistance>")
    End With

    GetDistance = Val(s(1))

End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
    Dim t As String
    Dim re As XMLHTTP

    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=[COLOR="#FF0000"]YOUR_MS_KEY[/COLOR]"

    Set re = New XMLHTTP
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re
        s = Split(.responseText, "<TravelDuration>")
    End With

    GetTimeinMins = Val(s(1)) / 60

End Function

use as

=GetDistance("PO144HZ","PO155TT")
 
Upvote 0
Hi, thanks for sharing this!

So I used this code ad the distance computed was zero (0). Any idea what could be wrong?
 
Upvote 0
I did so. Modified the code as follows:
1. Deleted the "
2. Referenced MSML

and I got m API key. I'm using Canadian postal codes (which I assume is equally applicable: "T2P 4R5", "M9N 1K9"
 
Upvote 0
I have updated the two functions to parse the XML response using the XML library, instead of the Split function, and return the distance and time.

Note - this code uses early binding of the XML library and therefore it requires a reference to Microsoft XML, v6.0. Set this via Tools -> References in the VBA editor.

Code:
Const APIkey = "YourAPIkey"

Function GetDistance(sPCode As String, ePcode As String) As Double

    Dim t As String, s As Variant
    #If VBA7 Then
        Dim re As XMLHTTP60
        Set re = New XMLHTTP60
    #Else
        Dim re As XMLHTTP
        Set re = New XMLHTTP
    #End If
   
    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey
   
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4
   
    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetDistance = .SelectSingleNode("//ns:TravelDistance").Text
        Debug.Print .SelectSingleNode("//ns:DistanceUnit").Text
    End With
   
End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
    Dim t As String, s As Variant
    #If VBA7 Then
        Dim re As XMLHTTP60
        Set re = New XMLHTTP60
    #Else
        Dim re As XMLHTTP
        Set re = New XMLHTTP
    #End If
   
    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey
   
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4
   
    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetTimeinMins = .SelectSingleNode("//ns:TravelDuration").Text / 60
        Debug.Print .SelectSingleNode("//ns:DurationUnit").Text
    End With
   
End Function

=GetDistance("T2P 4R5", "M9N 1K9") returns 2240.836018 (miles) for me.

Edited 29-Mar-2023 - removed forum's HTML markup around #If ... #Else ... #End If lines.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top