Regular Expresion Help

[TFCJamieFay]

Hi All,

I'm trying to create a filter that will stop any duff postcodes being entered by the end user. I have this piece of code that I found online, but it doesn't work for all postcodes.

The problem I have is that it doesn't work for postcodes in central London.

i.e. For the code EC1A 3RF, the "A" after the "1" can be any of the following"A,M,N,R,V or Y". I have changed the pattern to allow this but it doesn't work.

Can anyone see why as I'm tearing my hair out...?!

'This is the expression that validates the postcode
RgExp.Pattern = "(?:(?:AL|B[ABDHLNRS]?|" _
        & "C[ABFHMORTVW]|D[AEHLNTY]||EC?[AMNRVY]|E[NX]?|FY|GL|GU|" _
        & "H[ADGPRUX]|I[GP]|KT|L[ADELNSU]?|M[EK]?|" _
        & "N[EGNPRW]?|O[LX]|P[ELOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
        & "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO)" _
        & "\d(?:\d|[A-Z])? \d[A-Z]{2})"
    
'Does the fed in string match the pattern?
If RgExp.Test(strInput) = True Then
        IsUKPostcode = "Valid"
Else

Many thanks,

Jay

Ps. There are also other postcodes such as EC2? as well but I've only started on this one for now.

 

[Scott Huish]

I found this code here:
http://www.tek-tips.com/faqs.cfm?fid=6344

The samples you gave work with the code from the above link.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>EC1A 3RF</TD><TD>Valid</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>EC1M 3RF</TD><TD>Valid</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>EC1N 3RF</TD><TD>Valid</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>EC1R 3RF</TD><TD>Valid</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>EC1V 3RF</TD><TD>Valid</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>EC1Y 3RF</TD><TD>Valid</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>EC2A 3RF</TD><TD>Valid</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=ISukpostcode(A1)</TD></TR><TR><TD>B2</TD><TD>=ISukpostcode(A2)</TD></TR><TR><TD>B3</TD><TD>=ISukpostcode(A3)</TD></TR><TR><TD>B4</TD><TD>=ISukpostcode(A4)</TD></TR><TR><TD>B5</TD><TD>=ISukpostcode(A5)</TD></TR><TR><TD>B6</TD><TD>=ISukpostcode(A6)</TD></TR><TR><TD>B7</TD><TD>=ISukpostcode(A7)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[TFCJamieFay]

Thanks for the help HotPepper but that was the site I got the code from originally. I wanted to adapt it to only accept valid postcodes. That pattern accepted them all. I have managed to solve my puzzle now though. He is my code. This restricts the user to only valid UK postcodes, i.e. it will accept EC1V 3RF but not EC1Z 3RF as the one with "Z" in doesn't exist.

RgExp.Pattern = "(((^[BLMNS][1-9]\d?)|" _
                & "(^(AL|B[ABDHLNRS]|C[ABFHMORTVW]|D[AEHLNTY]|E[NX]|FY|G[LUY]|" _
                & "H[ADGPRUX]|I[GP]|KT|L[ADELNSU]|M[EK]|N[EGNPR]|O[LX]|" _
                & "P[ELOR]|R[GHM]|S[AEGKL-PRSTWY]|T[ADFNQRSW]|UB|W[ADFNRSV]|YO|ZE)\d\d?)|" _
                & "(^E[2-9])|" _
                & "(^E1[W0-9])|" _
                & "(^EC[5]0)|" _
                & "(^EC[1][AMNRVY])|" _
                & "(^EC[2][AMNPRVY])|" _
                & "(^EC[3-4][AMNPRV])|" _
                & "(^N1[1-9])|" _
                & "(^NW1[W01])|" _
                & "(^NW[1-9])|" _
                & "(^SW1[AEHPVWXY0-9])|" _
                & "(^SW[2-9])|" _
                & "(^W[2-9])|" _
                & "(^W1[A-HJKSTUW0-4])|" _
                & "(^WC[1][ABEHNRVX])|" _
                & "(^WC[2][ABEHNR])" _
                & ")(\s*)?" _
                & "([0-9][ABD-HJLNP-UW-Z]{2}))$|" _
                & "(^GIR\s?0AA$)"

Note: I have also excluded Scottich postcodes.

HTH,

Jay