Remove all Non UK or incorrectly formatted postcodes from column

[Lorr81]

Hi,

I have a sheet that has postcodes from all over the globe not to mention some really bad data entry, what I need to do is remove these leaving only the correctly formatted UK post codes.

I have tried and looked every where for a macro, formula or conditional formatting trick but with not joy, is this even possible and if so any advice would be greatly appreciated.

Thank you,

Lorraine

 

[Fluff]

It's solved that problem, but there are others such as

Excel 2013 32 bit
	A
2	EC2P 2FF
3	N1 4RZ
4	N1 5BB
5	N1 5TZ
6	N1 6DU
7	N1 6WW
8	N1 7ZZ
9	N14 4ZD
10	N14 7WN
11	N16 6UG
12	N16 7HP
13	N16 7SW
14	N16 8LA
15	N16 8SZ
16	N16 8ZF
17	N17 9NQ
18	N17 9PE
19	N17 9YZ
20	N17 9ZU
21	N18 1WP
22	N18 2RX
23	N18 9BL
24	N20 0XU
25	N20 9AY
Pcode

 

[kgkev]

You must have an extensive list of postscodes Fluff.. i tried to download a list of all postcode to check everything but it was an 800mb file.

EC2P 2FF appears to be geniunely invalid. Doesn't give a google maps result.

Fixed the 'N#' issue with this revision.

Function VPC(ByVal PostCode As String) As BooleanDim Sections() As String
    PostCode = UCase$(PostCode)
    Sections = Split(PostCode)
    If (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
        VPC = ((Sections(0) Like "[BEGLMNSW]#*" 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[HNX]#*" Or _
                          Sections(0) Like "E[C]#[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
        VPC = False
    End If
End Function

if EC2P is valid maybe its a new postcode area?

Function VPC(ByVal PostCode As String) As BooleanDim Sections() As String
    PostCode = UCase$(PostCode)
    Sections = Split(PostCode)
    If (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
        VPC = ((Sections(0) Like "[BEGLMNSW]#*" 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[HNX]#*" Or _
                          Sections(0) Like "E[C]#[AMNPRVY]" 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
        VPC = False
    End If
End Function

 

[Lorr81]

Hi,

I have created a new module in the workbook and using the code above and also tried the link provided and then used the condition formatting =(1-ValidPostCode(A2))*(A2<>"") really not my strong point any of this.

is it not possible to say if cells not formatted as XXX XX then delete?

Thank You

Lorr

 

[Norie]

Lorr

Once you have the function(s) in a module put either of these in row 2 of an empty column

=VPC(A2) kgkev's suggested function

or

=IsUKPostCode(A2) my suggested function

and copy down.

You should now be able to filter to only show the rows that don't have valid postcodes and then delete them.

 

[kgkev]

if you're using the latest code you need to change your function to

=vpc(a1)


try that and see if you can get a true or false for postcodes.



The problem with a simpler method is there are 6 valid postcode formats.

AA9A 9AA
A9A 9AA
A9 9AA
A99 9AA
AA9 9AA
AA99 9AA