I have two postcodes in two columns in Excel. I want the distance between the two in a third column.

There are a couple of approaches. One could try and get the longitude and latitude from the postcodes and then calculate an as-the-crow-flies distance mathematically. You could try pasting them into co-pilot and asking it to make an estimation (this would probably work but I had many thousands of rows and co-pilot doesn’t tend to like that).

Fortunately the Google Maps Distance Matrix API offers a method which can take two postcodes and return a distance between them.

Get a Google Maps API developer account (if you don’t have one).

You will need to enter credit card details for this but Google will allow 10,000 API calls per month before charging. This gives you access to Google Maps APIs including the Distance Matrix https://console.cloud.google.com/apis/library/

Google gives an example API call in the form of a url like this:

https://maps.googleapis.com/maps/api/distancematrix/json?destinations=New%20Yok%20City%2C%20NY&origins=Washington%2C%20DC&units=imperial&key=YOUR_API_KEY

Pop this into the address bar of a browser and you can see the raw XML response of the API call:

{
   "destination_addresses" : 
   [
      "New York, NY, USA"
   ],
   "origin_addresses" : 
   [
      "Washington, DC, USA"
   ],
   "rows" : 
   [
      {
         "elements" : 
         [
            {
               "distance" : 
               {
                  "text" : "228 mi",
                  "value" : 367303
               },
               "duration" : 
               {
                  "text" : "3 hours 49 mins",
                  "value" : 13712
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

Once you have it working, the next step is to get Excel calling this as part of a function.

Create a UDF (User Defined Function) in Excel that calls the Google Maps API

The function will have 2 arguments, Origin and Destination. I am using UK postcodes but the Origin and Destination could be any string that Google Maps will recognise (cities, places of interest etc).

Function G_DISTANCE(Origin As String, Destination As String) As String
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
G_DISTANCE = 0

Origin = Replace(Origin, " ", "%20")
Destination = Replace(Destination, " ", "%20")
Set myRequest = New XMLHTTP60
myRequest.Open "GET", "https://maps.googleapis.com/maps/api/distancematrix/json?destinations=" _
        & Destination & "&origins=" & Origin & "&units=metric&key=YOUR_API_KEY", False
myRequest.send
    
Dim response As String
response = myRequest.responseText
Dim response1Line As String
response1Line = Replace(Replace(response, vbLf, ""), "  ", "")
Dim distanceStr As String
distanceStr = Mid(response1Line, InStr(response1Line, "distance") + 22, 6)

G_DISTANCE = distanceStr

End Function

The above function takes Google’s XML response and turns into a single string. Then it identifies the part we were looking for (the bit after the word “distance”).

Invoke the function in Excel

You can now use the function in Excel to get the distance between 2 postcodes in cells A2 and B2 like so:

=G_Distance(A2,B2)