Help with Validation of UK Postcodes all formats - giving some incorrect results

[c50]

Hello,

I needed help to check whether a UK postcode is correct so I followed the guides on this forum (the link below). I copied the code into Excel to help check 45539 postcode entries.

Validation of UK Postcodes all formats

UK Postal Code Validation


It seems to work well. However; it is showing a lot of FALSE or incorrect postcodes whereas they are perfectly fine. I don't really understand the coding so why is it doing this? Is there something missing in the code for certain postcodes?

I've uploaded a small spreadsheet with 20 examples below

Click here for spreadsheet with the code used

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

 

[Fluff]

Hi & welcome to MrExcel.
How about

Function ValidPostCode(ByVal PostCode As String) As Boolean
Dim Sections() As String
    PostCode = UCase$(PostCode)
    Sections = Split(PostCode)
    If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
        (Sections(1) Like "#[A-Z][A-Z]" And _
        (Sections(0) Like "[A-Z]#" Or Sections(0) Like "[A-Z]#[0-9ABCDEFGHJKMNPRSTUVWXY]" Or _
         Sections(0) Like "[A-Z][A-Z]#" Or Sections(0) Like "[A-Z][A-Z]#[ABCDEFGHJKMNPRSTUVWXY]")) Then
        ValidPostCode = ((Sections(0) Like "[BEGLMNSW]#*" Or _
                          Sections(0) Like "A[BL]#*" Or _
                          Sections(0) Like "B[ABDFHLNRST]#*" Or _
                          Sections(0) Like "C[ABFHMORTVW]#*" Or _
                          Sections(0) Like "D[ADEGHLNTY]#*" Or _
                          Sections(0) Like "E[CHNX]#*" Or _
                          Sections(0) Like "F[KY]#*" Or _
                          Sections(0) Like "G[LU]#*" Or _
                          Sections(0) Like "H[ADGPRSUX]#*" Or _
                          Sections(0) Like "I[GPV]#*" Or _
                          Sections(0) Like "K[ATWY]#*" Or _
                          Sections(0) Like "L[ADELNSU]#*" Or _
                          Sections(0) Like "M[EKL]#*" Or _
                          Sections(0) Like "N[EGNPRW]#*" Or _
                          Sections(0) Like "O[LX]#*" Or _
                          Sections(0) Like "P[AEHLOR]#*" Or _
                          Sections(0) Like "R[GHM]#*" Or _
                          Sections(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
                          Sections(0) Like "T[ADFNQRSW]#*" Or _
                          Sections(0) Like "W[ACDFNRSV]#*" Or _
                          Sections(0) Like "UB#*" Or _
                          Sections(0) Like "YO#*" Or _
                          Sections(0) Like "ZE#*") And _
                          Sections(1) Like "*#[!CIKMOV][!CIKMOV]")
    Else
        ValidPostCode = False
    End If
End Function

This is not quite as robust as the original & does not check all the special codes.

 

[c50]

Hello and thank you Fluff!

I can't wait to try that in the morning. What did you add or change? Was the original code too old and couldn't cope with those postcodes in the examples?

I did try to read the code but I couldn't understand it. There seems to be conditions in the [ ] brackets? so I assume it's not simply a postcode format checker. If only there were comments on how it works as I could see this being very useful for a lot of people

 

[Fluff]

I mainly removed the 2nd bit in [ ] from this line

Sections(0) Like "E[CHNX]#[AMNRVY]" Or _

and updated a couple of other bits.
By the way the 1st code you showed is not valid.

 

[c50]

I mainly removed the 2nd bit in [ ] from this line

Sections(0) Like "E[CHNX]#[AMNRVY]" Or _

and updated a couple of other bits.
By the way the 1st code you showed is not valid.

Not sure which part is not valid? I assume that only the letters that are inside the E[CHNX] are acceptable combinations with E?

Am I being naive or is there a way simpler method to just check the validity of postcode formatting? Somebody must have asked the same question

I just found a more recent thread but the code seems more complex ?

 

[Fluff]

I meant the first of the postcodes in your image CF6 1VH is not a valid postcode.
The other code you linked to doesn't validated the inward code properly.

 

[Rick Rothstein]

I am guessing Fluff's change has to do with some updates in what are now valid postcodes. If so, then the modified function is returning correct results. The original code you posted was a modification of code I posted many, many years ago and was believed to be correct back then. There are certain combinations of letters and numbers that are not valid together in UK postcodes... and there is no simple way to do a validation on them. If you have doubts as to a postcode's validity as returned by the macro, you can check individual post codes here... Validate A UK Postcode - UK Postcode

 

[Rick Rothstein]

The original code you posted was a modification of code I posted many, many years ago and was believed to be correct back then.

For those who might be interested, this was my original code...

Function ValidatePostCode(ByVal PostCode As String) As Boolean
  Dim Parts() As String
  PostCode = UCase$(PostCode & " ")
  Parts = Split(PostCode)
  ValidatePostCode = (PostCode = "GIR 0AA " Or PostCode = "SAN TA1 " Or _
              (Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _
              (Parts(0) Like "[A-PR-UWYZ]#" Or _
               Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _
               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _
               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
End Function

The code you posted modified and expanded the checks I did... that modified code was written by a man named Bob Phillips. Just so you know, I found a reference for my code dated in 2012 and I wrote at that time that I had posted it several years ago.

 

[c50]

I meant the first of the postcodes in your image CF6 1VH is not a valid postcode.
The other code you linked to doesn't validated the inward code properly.

Ah yes, I see that CF6 1VH is invalid. Your code amendments have reduced the size of my list so thank you! The ones that do look ok in format are actual non working postcodes like that 1st postcode. Thank you for your help Fluff

 

[c50]

I am guessing Fluff's change has to do with some updates in what are now valid postcodes. If so, then the modified function is returning correct results. The original code you posted was a modification of code I posted many, many years ago and was believed to be correct back then. There are certain combinations of letters and numbers that are not valid together in UK postcodes... and there is no simple way to do a validation on them. If you have doubts as to a postcode's validity as returned by the macro, you can check individual post codes here... Validate A UK Postcode - UK Postcode

Thanks for starting Rick. It's a very useful function and I am sure it has helped many people out.

I'm happy that the modified vb code that Fluff posted works. I am in awe and would love to understand how it all works