INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

Validate a UK Postcode by idbr
Posted: 14 Jul 06 (Edited 19 Jul 06)

This function will validate a given UK postcode, and will also supply a corrected postcode for a range of common typo errors.

CODE

Public Function IsUKPostCode(strInput As String)

'Uses a regular expression to validate the format of a postcode.
'May require WindowsScripting 5.6 - downloadable from microsoft.com

Dim RgExp As Variant

'Create the regular expression object
Set RgExp = CreateObject("VBScript.RegExp")

'Clear the function value
IsUKPostCode = ""

'Check we have value to test
If strInput = "" Then

    IsUKPostCode = "Not Supplied"
    
    Exit Function

End If
    
    'This is the ridiculously complex expression that validates the postcode
    RgExp.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
                & "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
                & "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
                & "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
                & "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
                & "\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
    
        '------------------------------
        'Try to make a correct postcode
        '------------------------------
         
        'Despace & uppercase
        strInput = UCase(Replace(strInput, " ", ""))
         
        'Clean out any redundant characters - whilst most of these don't make sense
        'I've seen them all in postcodes before!
        strInput = Replace(strInput, "_", "")
        strInput = Replace(strInput, ",", "")
        strInput = Replace(strInput, "+", "")
        strInput = Replace(strInput, "-", "")
        strInput = Replace(strInput, ":", "")
        strInput = Replace(strInput, "=", "")
        strInput = Replace(strInput, "/", "")
        strInput = Replace(strInput, "*", "")
        strInput = Replace(strInput, "?", "")
         
        '---------------------------------------------------------------------------
        'Check the string length again to make sure we've not got a "???" type entry
        '---------------------------------------------------------------------------
        
        If Len(strInput) = 0 Then
        
            IsUKPostCode = "Not Supplied"
            Exit Function
            
        ElseIf IsNumeric(strInput) Then
        
            IsUKPostCode = "All Numbers"
            Exit Function
            
        ElseIf Len(strInput) < 6 Then
        
            IsUKPostCode = "Too Short"
            Exit Function
                    
        End If
         
        'Check for and correct substituted O (alpha) for 0 (numeric) at position len - 2
        If Mid(strInput, Len(strInput) - 2, 1) = "O" Then strInput = _
        Left(strInput, Len(strInput) - 3) & "0" & Right(strInput, 2)
        
        'Check for and correct substituted 0 (numeric) for O (alpha) at position 1 or 2
        If Mid(strInput, 2, 1) = "0" Then strInput = _
        Left(strInput, 1) & "O" & Right(strInput, Len(strInput) - 2)
        
        If Left(strInput, 1) = "0" Then strInput = _
        "O" & Right(strInput, Len(strInput) - 1)
        
        'Check for and correct substituted lowercase l for 1 at position len - 2
        If Mid(strInput, Len(strInput) - 2, 1) = "l" Then strInput = _
        Left(strInput, Len(strInput) - 3) & "1" & Right(strInput, 2)
        
        'Check for and correct substituted lowercase l for 1 at position 3
        If Mid(strInput, 3, 1) = "l" Then strInput = _
        Left(strInput, 2) & "1" & Right(strInput, Len(strInput) - 3)
        
        'Check for and correct substituted S for 5 at position len - 3
        If Mid(strInput, Len(strInput) - 3, 1) = "S" Then strInput = _
        Left(strInput, Len(strInput) - 3) & "5" & Right(strInput, 2)
         
        'Two possible lengths for a valid UK postcode
        Select Case Len(strInput)
                 
        Case 6
             
            If RgExp.test(Left(strInput, 3) & " " & Right(strInput, 3)) = True Then
                 
                 'Format should be ?## #?? or ??# #??
                 IsUKPostCode = Left(strInput, 3) & " " & Right(strInput, 3)
                 
            Else
            
            IsUKPostCode = "Invalid"
                 
            End If
         
        Case 7
        
             If RgExp.test(Left(strInput, 4) & " " & Right(strInput, 3)) = True Then
             
                 'Format is ??## #?? or ?#?# #??
                 IsUKPostCode = Left(strInput, 4) & " " & Right(strInput, 3)
                 
            Else
            
                IsUKPostCode = "Invalid"
         
            End If
            
        Case Else
        
            IsUKPostCode = "Invalid"
             
        End Select
        
    End If
    


End Function

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close