Excel UDF to validate UK Postcode

As part of our postcode analysis and cleansing processes, an important task is to ensure that a given postcode is actually a valid UK format postcode. It is not easy to spot errors or potentially invalid postcodes when you have a large list of addresses, especially if you have used OCR scanning that may mis-recognise characters in postcodes such as O for zero, l for I etc. Each postcode related project we deal with is rigorously cleaned and tested to ensure absolute accuracy.

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.

The UDF (User defined function) below should be copied exactly as is and placed in a module in your VBA. You can then use a formula on any worksheet to refer to a cell that contains a postcode and the result will return either valid or invalid. As its a formula, as you change the postcode value in a cell, the result updates immediately.

You will need to use this formula:

=validatepostcode(A1)

…which will return either Valid or Invalid for the postcode in cell A1.

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



Function ValidatePostCode(ByVal PostCode As String)
'datanology UK Postcode Validator
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*")
End If
If ValidatePostCode Then
ValidatePostCode = "Valid"
Else
ValidatePostCode = "Invalid"
End If
End Function






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

5 thoughts on “Excel UDF to validate UK Postcode

  1. John says:

    Hi Datanology,

    The function seems to be perfect for what I want to do (Validate whether a postcode is correct and if not display a box stating ‘ Invalid Postcode’ when type into lets say A1.
    However when I open a new module and copy it across, I cant seem to get it working when I run Macro. I’m very new to it all :).
    Any ideas on what I can do
    Thanks,

    John

    • Datanology says:

      Hi David,

      The VBA will need to be pasted into a module in the VB Editor (Alt + F11). This then creates a function in normal excel worksheet use that you can use just like other formulas. For instance if you put a valid postcode in cell A1 and then next to it in B1 put the formula:

      =validatepostcode(A1)

      The result will be ‘Valid’

      All you need to do is make sure that the Cell reference in brackets (A1) in the example, points to the cell you want to check.

    • Datanology says:

      Hi Tim, the font in the web browser had set the speech marks “” incorrectly. I’ve adjusted it now so that this won’t happen and I’ve tested the function from copying and pasting directly from here and all good. Thanks for letting me know.

  2. Pingback: Postcode to Town and City | Datanology

Leave a Reply

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