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

 

[c50]

I think I spoke too soon... I'm still getting 35000 False results and a lot of them are actually correct.

Is there a corrected more updated version of this on the forum? I searched and can't seem to find anything

 

[Fluff]

Can you post a sample of some of those false results

 

[c50]

Can you post a sample of some of those false results

Small sample here

Here's a snippet. It's a 45000+ spreadsheet so I just copied a random section

 

[Fluff]

Ok, thanks for that. 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]#[0-9ABCDEFGHJKMNPRSTUVWXY]")) 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

 

[c50]

Thanks! That only returned 335 FALSE and 427 #VALUE! of 45538 entries. I randomly tested 20 and it seems to correct!

I now have to manually amend each member's record... ?

I would never have been able to do this without this amazing vb code.

Thank you very much for your help Fluff! And Rick for starting all this code off ?

 

[Fluff]

Glad we could help & thanks for the feedback.