Andrew… I’ve got a list of postcodes… Is there a way to use Excel to find out which local authority they are all in…?
Yes… but first a few caveats! (or just skip to the code)
- Excel is a “quick and dirty” solution – If you are doing this properly you’d use GIS, but Excel will give you a “close enough” idea.
- Postcodes are “complicated” for geography – they were designed for delivering post and not a lot else!
There are some postcode areas that straddle local authority areas (even countries!).
Some are “discontinued” or non-geographic (for instance PO boxes).
Both postcodes and administrative boundaries can move or change.
Postcodes can be very different sizes – which is why you often need directions even when using a Sat Nav to navigate to a rural location.
Get your data
The reason I prefer Doogal as a source is that it allows you to download just bits of the data (in particular only postcodes that are currently valid). One of the issues is that the postcode database is very large (too big in it’s entirety for Excel…). The ONS does allow you to open the specific postcode areas, but you have to download the whole 200MB zip file.
Before you try and match this data with your own list of postcodes, try and focus the dataset a bit more – if you only have addresses in Surrey, then clean the dataset to Surrey only! In this case “filters” in Excel are your friend – filter the cells you want to delete then “select all” and “delete”. Finally “remove duplicates” to rid the spreadsheet of blank rows.
Doogal can also be a helpful tool to understand the postcodes in the specific area you are looking at – there are lots of useful maps and other resources.
Understand your data
Here’s the data for the Twickenham postal area from doogal:
There are a few different columns there (it includes police force, NHS data and many others) – but I have highlighted the ones of interest to us for this how-to.
You also need to be clear which column you want – you may need to grab both “Unitary” (column H) and “District” (Column I) depending on what your specific interest is. The MAP MEN can explain the different levels of admin better than I can!
Now look at your own data and check that there is a suitable field to match:
Annoyingly our dataset hasn’t got the postcodes separated – but luckily we can add some code to sort that:
This FINDs the last “, ” in the address (A2), (starting the search 10 characters from the end (LEN)) and then uses MID to return the 8 characters after that, which is the postcode (the “+2” is because “, ” is two characters long).
The two columns you are matching from your own data and the postcode lookup need to be exactly the same format (watch out for commas, double spaces or different cases).
Index Match your data!
Our sheets are called “my_data” and “postcodes” and the code we need is:
The MATCH function searches the A column of the postcodes for the postcode from my data we are looking for. The 0 means “exact match”. It returns a row number, which is used in the INDEX function.
The INDEX looks for the local authority (column I) at the intersection of the row (MATCH) and column (1).
The $ symbol is an absolute rather than relative reference (so if you drag the code into a different cell, it should still try to match the postcode).