With the help of Google's map api and some C# code, I have developed an addin for Excel 2003,2007 and 2010 that will return various bits of info if supplied with postcodes. The functions and their uses are:
Gets kilometres between two postcodes
Gets miles between two postcodes
Gets Latitude for postcode e.g. NN14 2JD = 52.4472546
Gets Longitude for postcode e.g. NN14 2JD = -0.82291
Gets duration in hours and decimal of hours e.g. 1.2 is actually
1hr 12mins N.B.
Please remember: these functions will ONLY work with UK postcodes.
An example: =GetMiles("NN147RJ","ST52SL") will return 108.01 miles.
***The postcodes need to have double quotes around them as above***
The data is fetched from Google's maps api so please remember that if you are using these formulae for a 1000-line spreadsheet - it may take a while! Needless to say you need an internet connection for this to work and you will need to have .Net Framework 4 installed or it won't work! (Link here)
You can download the adding from here: Excel Mileage Addin
Now for the slightly tricky bit.
BEFORE YOU COPY THE ADDIN TO THE ADDIN FOLDER EXCEL MUST BE CLOSED
When you have the addin, you need to copy it to your addins folder which is usually:
C:\Documents and Settings\USERXXX\Application Data\Microsoft\AddIns
The 'USERXXX' will be your username for Windows - if you get stuck scout the internet for info on this.
Once the addin is copied over, you will need to activate it in Excel:
For Excel 2003 you can go to 'Tools' menu and then 'Addins' - 'ExcelMileage' should be listed, just make sure there is tick against it.
For Excel 2010 you need to go to 'File'->'Options'->'Add-Ins', ExcelMileage should be listed under 'Active Application Add-ins' and you should need to do nothing more. If you have probs, go to the 'Manage:' box at the bottom, select 'Excel Addins' and make sure 'ExcelMileage' is enabled.
And that should be it! The formulae will handle postcodes in various formats - e.g. "NN46HG" or "nn4 6hg" but please remember it will only work for UK postcodes.
Post if you need assistance.