UK Postal Code Validation

[JuExcel]

Hi, I am looking to validate UK Postal Code using MS Excel formula, can anyone help!!! The postal code is should be in the following format:

AN NAA
ANN NAA
AAN NAA
ANA NAA
AANA NAA
AANN NAA

When A is alphabet and N is number. If the condition is incorrect then highlight that particular cell that contains the incorrect postcode.
Many thanks in advance.

 

[xld]

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

 

[JuExcel]

Hi, thanks for the reply appreciate it. to be honest I don't Know how to run this code and I am looking for a formula like this:

=AND(LEN(A1)=6,CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,ISNUMBER(--MID(A1,2,1)),MID(A1,3,1)=" ",ISNUMBER(--MID(A1,4,1)),CODE(MID(A1,5,1))>64,CODE(MID(A1,5,1))<91,CODE(RIGHT(A1,1))>64,CODE(RIGHT(A1,1))<91)

this will validate AN NAA and I can change it to do the other validation but can not put them to gether in one formula. can you help? Thanks

 

[Richard Schollar]

The function that Xld has provided goes beyond what you requested in that it performs a complete validation according to existing UK post codes (well, looks that way to me anyhow).

With your workbook that contains the postcodes you want to validate open and visible on your Excel screen, use Alt+F11 to open up the Visual Basic Editor, then use Alt+I+M to insert a standard module in your workbook's Project, then paste in the above function code Xld has provided.

Then, go back to your sheet, Select all your postcodes (eg A2:A1000) and go Format>Conditional Formatting>change to Formula Is and use the formula:

=(1-ValidPostCode(A2))*(A2<>"")

and hit the Format button and select a red pattern. Then click OK to finish.

Any cells failing the test will now be coloured red.

 

[JuExcel]

Thanks for the instructions on how to run that function, I have managed to run the function but it fails to highlight the most of the incorrect postcode and highlighted some of the correct postcode.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
As I mentioned earlier to validate AN NAA<o></o>
I have:<o></o>
<o> </o>

=AND(LEN(A1)=6,CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,ISNUMBER(--MID(A1,2,1)),MID(A1,3,1)=" ",ISNUMBER(--MID(A1,4,1)),CODE(MID(A1,5,1))>64,CODE(MID(A1,5,1))<91,CODE(RIGHT(A1,1))>64,CODE(RIGHT(A1,1))<91)<o></o>​

<o> </o>
And to validate ANN NAA<o></o>
I have: <o></o>

<o> </o>​

=AND(LEN(A1)=7,CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,ISNUMBER(--MID(A1,2,2)),MID(A1,4,1)=" ",ISNUMBER(--MID(A1,5,1)),CODE(MID(A1,6,1))>64,CODE(MID(A1,6,1))<91,CODE(RIGHT(A1,1))>64,CODE(RIGHT(A1,1))<91)​

<o> </o>​

Now I want to join these two formulas together and only give TRUE if the data in cell A1 meets the condition of one of the above formulas. Any help would be appreciated. Many thanks ​

 

[xld]

Hi, thanks for the reply appreciate it. to be honest I don't Know how to run this code and I am looking for a formula like this:

=AND(LEN(A1)=6,CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,ISNUMBER(--MID(A1,2,1)),MID(A1,3,1)=" ",ISNUMBER(--MID(A1,4,1)),CODE(MID(A1,5,1))>64,CODE(MID(A1,5,1))<91,CODE(RIGHT(A1,1))>64,CODE(RIGHT(A1,1))<91)

this will validate AN NAA and I can change it to do the other validation but can not put them to gether in one formula. can you help? Thanks

I wouldn't do that, just plug that function into the VBE and use

=ValidPostCode(A1)

it will return True or False