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

 

[Norie]

Lorraine

Perhaps you could try the function https://www.tek-tips.com/faqs.cfm?fid=6344 to identify valid UK post codes.

 

[kgkev]

Try this thread

https://www.mrexcel.com/forum/excel-questions/436118-uk-postal-code-validation.html

 

[Fluff]

FYI The function created by xld in the link, that kgkev supplied does not work.
Also, you need to be aware that other countries also use similar Postcodes to the UK.

 

[kgkev]

really? it worked fine for me

[TABLE="width: 167"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]AB25 4LP[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]S1 7JW[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]EC2N 4AG[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]E1B 4WE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]WR8 7QP[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]Wr8 7Qp[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]WW4 AAA[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]WW AAA[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

I got errors on these (amongst others)

Excel 2013 32 bit
	A
2015	EN9 1AL
2016	EN9 2RZ
2017	EN9 2SQ
2018	EN9 3JR
2019	EN9 3PU
2020	EN9 3TY
2021	EN9 3YZ
2022	EN9 3ZT
2023	EX10 9XZ
2024	EX10 9YF
2025	EX10 9YN
2026	EX11 1SG
2027	EX11 1ZU
2028	EX12 3RB
2029	EX13 5UW
Pcode

 

[kgkev]

You're right. It seems the section to account for EC1A doesn't allow for EX11 or EN9

Function VPC(ByVal PostCode As String) As Boolean
Dim 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 "[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[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

That should fix it.

 

[Lorr81]

Hi All,

Thank you for the suggestions however it is still not working, I am getting no results at all, have I done something wrong?

Lorr

 

[kgkev]

Have you added this function into a new module on the workbook?

 

[Norie]

Lorr

What exactly have you tried?

The functions that have been suggested would be used like standard Excel worksheet functions to tell you if a postcode was valid or not.

For example to test if the postcode in A1 was valid you would use this.

=VPC(A1)