Distance between Postcodes in Excel

Distance between Postcodes in Excel

Didn't find your answer?

I would like to calculate the distances between postcodes (crow flies would do).  Is there a way to do this in Excel.  I can list the codes in 2 columns 

Replies (14)

Please login or register to join the discussion.

Stepurhan
By stepurhan
21st Nov 2013 12:02

Not easily

If you were to put the longitude and latitude of every postcode in, it could be done. Otherwise, the varying areas postcodes cover, usually based on how dense the properties are, make it a non-starter with just the codes themselves.

Why not just use an online service, like Google Maps? Using that I've just determined it is 273 miles from HMRC Bradford to Companies House Cardiff driving.

Thanks (0)
avatar
By GazCol
21st Nov 2013 12:16

Easy - MapPoint

You could import to MS MapPoint from Excel to achieve this and then export back to Excel.

Thanks (0)
avatar
By alan.falcondale
27th Nov 2013 11:40

northings and eastings

If you subscribe to the postcode data (as we do) it is relatively easy to resolve the distances in Excel.

We do a lookup of the northings and eastings for each postcode then work from the values given.

This does of course give a straight line distance but over sets of data and areas we have found that a localised percentage can be reliably used for estimation of road distances in the first instance just to sanity check.

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
12th Dec 2013 13:22

Very interesting question

I can see that this situation could crop up if you had to calculate mileages for deliveries or expenses, which suggests that a road distance mechanism would be even more helpful than as the crow flies.

@GazCol - do you think MS MapPoint would be up to this. And if it is, do you think you'd be able to wrangle this? It would make a great import/export and programming tutorial for our Excel Zone!

Thanks (0)
Replying to Melody:
avatar
By GazCol
12th Dec 2013 14:14

Hi John,

Yes, MS MapPoint is able to give distances by actual travel, not just how the crow flies. Particularly handy if you've got lots of expenses to file and don't want to be going back and forth from Google Maps to work out the distances.

Where it tends to get used more often is business intelligence; it's a great way to visualise customer, sales and competitor data. Google have really missed a trick in not being able to readily import geo data from excel into Maps/Earth, unless you're working with WGS84 co-ordinates, and what kind of person uses that rather than postcodes?

I have a break between contracts of the Christmas/New Year period so I'll look at putting together a step-by-step guide via YouTube or simple screenshots for Excel Zone.

Thanks (0)
avatar
By PanelMaker
12th Dec 2013 14:24

Thanks looks interesting

Hi everyone

Thanks for your input.  I was only looking for rough figures to assess how far away a list of 100 sites were for quotation purposes and thought that there might be a database available that had latitude/longitude of postcodes that could be used to calculate crows flying figures.

If someone could come up with something, especially if it had driving distances, it would be great.

Thanks (0)
The triggle is a distant cousin of the squonk (pictured)
By Triggle
12th Dec 2013 14:38
Thanks (0)
James Reeves
By James Reeves
12th Dec 2013 15:03

More options
@PanelMaker The site http://www.freemaptools.com/ has various tools for calculating distances between postcodes and you can also download a CSV file of UK postcodes with their latitude and longitude coordinates from the site. The site also contains links to algorithms that show you how to calculate distances using the coordinates if you want to write your own code. The postcode data you can download comes in 2 flavours: outcodes or full postcodes.  The list of outcodes are manageble in size (80KB CSV) and contain the general outcode locations (the part of the postcode before the space). This can be quite large area and may not be fine enough for your calculations, depending on how you are using it. On the other hand the full postcodes list has very fine granularity; it goes down to the final postcode letter which usually represents just a few houses. This means that the files are very large (89MB CSV) and they have too many rows to load into Excel.   If you needed an interim granularity (e.g. down to say the first digit or first digit and first letter of the postcode after the space) you could obviously extrapolate this from the full postcode list and build a smaller more manageable list that would fit into Excel. There are a number of ways of achieving this - e.g. suck the list into a SQL or MS-Access database and crunch it using some simple queries to spit out a smaller subset. I haven't used the data for several years but according to the version history it does seem to be updated regularly enough.  I'm fairly sure there are other sites that offer the same data since it was made available through the Ordnance Survey OpenData initiative in 2010.  

Thanks (1)
avatar
By PanelMaker
12th Dec 2013 15:29

Brilliant

Thanks Triggle

This site is brilliant!

Paste a list of postcodes to find the UK Northings and Eastings, Copy them back to Excel and with a bit of O Level maths I got the diagonal mileages which are near enough for now.

The site also shows them all on a map which is useful for batching into areas.

 

Thanks (0)
The triggle is a distant cousin of the squonk (pictured)
By Triggle
12th Dec 2013 15:32

SOH CAH TOA

Thanks (2)
avatar
By User deleted
12th Dec 2013 16:22

Asked before and answered ....

However, cannot find the post in Aweb search (there's a surprise!)

Have a look at this - http://blog.acmultimedia.co.uk/2008/03/uk-post-code-distance-calculator-...

Technically, with another approach of all postcodes within an area (i.e. Rightmove way then because of the 'great circle' you need to use the Haversine Formula

Also you need to download a postcode file with lat & long - when I last looked it had 23k+ records in it

Just find an online calculator - much simpler

Thanks (0)
avatar
By Optegra
13th Dec 2013 16:11

Hi

I remember doing this a while ago to plan my trips around the uk. I will see if I can dig it out as it was quite need programming in VBA from memory...

Worked by having a 'from' and 'to' cell

Thanks

 

 

Thanks (0)
avatar
By Fran001
13th Dec 2013 16:15

Thanks for the giggle Triggle

SOH CAH TOA

Goodness that took me to my maths o level 25 years ago - Some old Hag cracked all her teeth on apples was the one we used, but I don't know if that's universal for that acromyn?

Very interesting post, thanks to all for responses.

 

 

Thanks (0)
avatar
By Gone Sailing
16th Dec 2013 19:59

Value Route Planning

A client today was using just Excel to cluster clients for route planning on a value (is the trip worth it) basis, I guess that's another step up.

MapPoint appears to carry stats but I don't see 3D.

 

Thanks (0)