UK Postcode formula for Excel

When dealing with UK addresses in Excel, having a clean database of clients, potential clients addresses is important to ensure that when you make contact with them you make sure that contact happens and shows your business in a professional way. Incorrect addresses, spelling errors etc can look unprofessional to a prospective client.

Using formulas in Excel to ‘clean’ and prepare address data is something that datanology take seriously and our cleansing processes use a multitude of formulas and VBA to make sure that any address database we clean is in absolute pristine condition when returned.

The most common and probably most useful formula for everyday use is splitting the Outer and Inner postcodes. With or without a space in the data, this formula will carry out the job of extracting the Outer part of the UK postcode which you can use to further analyse or prepare your address data with.

Our blog contains many other references for dealing with UK postcodes in Excel and a free download of a UK postcode database with over 1 million address records.

When put in any cell in Excel, the specific formula below will take a full UK postcode (with or without spaces) in cell A1 and output the Outer (first part) as the resulting value.

ie ‘KT13 8EJ’or ‘KT138EJ’ will be output as ‘KT13’

Feel free to ask us a question, or comment below…



=IF(ISERROR(LEFT(A1,LEN(A1)-3)),"",LEFT(A1,LEN(A1)-3))






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

2 thoughts on “UK Postcode formula for Excel

  1. Pingback: Postcode to Town and City | Datanology

  2. Clare says:

    If I was to have the full postcode with no spaces and I wanted to add a space in the correct position and keep the postcode how would I do this?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *