Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
Hi all,
I trying to do UK postcode validation. From a previous post by 'Toppers' I've got the code below. It looks good however it fails if a postcode of, for example, xxxx 4at. Does anyone have a better solution? Thanks, Ed Sub MyTest() ValidatePostCode "WX2 1BA" End Sub Sub ValidatePostCode(Byval PostCode as String) ' ' Validate UK Post Code ' Dim v As Variant Dim Outer As String, Inner As String Dim invalid As Boolean v = Split(PostCode, " ") If UBound(v) = 0 Then MsgBox "Post code " & PostCode & " does not contain a blank" Exit Sub End If Outer = UCase(Trim(v(0))) Inner = UCase(Trim(v(1))) ' ' Validate inner code ..... ' invalid = False If Len(Inner) < 3 Then invalid = True Else If Left(Inner, 1) Like "[0-9]" Then If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A- Z]" Then Else invalid = True End If Else invalid = True End If End If If invalid Then MsgBox "Inner code " & Inner & " is invalid" Exit Sub End If ' ' Validate Outer code ..... ' invalid = False Select Case Len(Outer) Case Is = 2 If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" Then Else invalid = True End If Case Is = 3 If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _ And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]") Then Else invalid = True End If Case Is = 4 If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _ And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]") _ And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like "[0-9]") Then Else invalid = True End If Case Else invalid = True End Select If invalid Then MsgBox "Outer code " & Outer & " is invalid" Exit Sub End If MsgBox "Post Code " & PostCode & " is valid" |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Postcode structure for data validation | Excel Discussion (Misc queries) | |||
How to make a spreadsheet idiot-proof | Excel Worksheet Functions | |||
Postcode Validation | Excel Discussion (Misc queries) | |||
How to tamper proof cells? | Excel Discussion (Misc queries) | |||
Postcode validation | Excel Discussion (Misc queries) |