Excel Formula or Macros - Distance Between Two Postal Codes

[LaurindaS]

Hi,

I'm from Canada & I'm trying to see if there is a formula or macros I can use to calculate the distance (KM) between two postal codes.
A1 - Postal Code 1
B1 - Postal Code 1
C1 - Distance (KM)
Any help would be greatly appreciated.

Thanks,
Laurinda

 

[LaurindaS]

Sorry, I just seen a typo above.

A1 - Postal Code 1
B1 - Postal Code 2
C1 - Distance (KM)

Thanks!

 

[sergioMabres]

Hi Laurinda,
I have an idea, but I do not have here the latest version of Excel, I need the WEBSERVICE function (see URL Below)
Then I create the URL by a simple concat formula, I am using the HYPERLINK function BC I do not have WEBSERVICE in my 2010 Excel

=HYPERLINK("https://www.google.com/search?q=what+is+the+distance+between+"&A2&"%2C+canada+and+"&B2&"%2C+canada+in+km")

Cod 1	Cod 2	URL
A1A1A1	X0B1B0	https://www.google.com/search?q=what+is+the+distance+between+A1A1A1%2C+canada+and+X0B1B0%2C+canada+in+km
A1A1A1	X0E1A0	https://www.google.com/search?q=what+is+the+distance+between+A1A1A1%2C+canada+and+X0E1A0%2C+canada+in+km

<tbody>
</tbody>

And extract the distance from the answer, I will finish this tomorow
Cheers
Sergio

https://support.office.com/en-us/article/webservice-function-0546a35a-ecc6-4739-aed7-c0b7ce1562c4

 

[LaurindaS]

That would be awesome! Thank you so much!

 

[LaurindaS]

I am on Excel 2007 so I don't think I would have the ability to use this option.

 

[John_w]

This can be done with the Google Directions API or Distance Matrix API. Code for the former is at https://www.mrexcel.com/forum/excel...ance-getgoogledistance-decimal-separator.html.

The linked code must be modified to use https instead of http and a "&key=Your_API_Key" must be added to the Google API URL. See https://developers.google.com/maps/documentation/directions/get-api-key to obtain a key and register your project/application. You must also enable billing and provide a credit card number, though Google give a $200 monthly credit.