Function UKPostCode(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(S, X, 6) Like "[A-Z]# #[A-Z][A-Z]*" Then
If ValidatePostCode(Mid(S, X, 6)) Then
UKPostCode = Mid(S, X, 6)
Exit For
End If
ElseIf Mid(S, X, 7) Like "[A-Z]## #[A-Z][A-Z]*" Or _
Mid(S, X, 7) Like "[A-Z][A-Z]# #[A-Z][A-Z]*" Or _
Mid(S, X, 7) Like "[[A-Z]#[A-Z] #[A-Z][A-Z]*" Then
If ValidatePostCode(Mid(S, X, 7)) Then
UKPostCode = Mid(S, X, 7)
Exit For
End If
ElseIf Mid(S, X, 8) Like "[A-Z][A-Z]#[A-Z] #[A-Z][A-Z]*" Or _
Mid(S, X, 8) Like "[A-Z][A-Z]## #[A-Z][A-Z]*" Then
If ValidatePostCode(Mid(S, X, 8)) Then
UKPostCode = Mid(S, X, 8)
Exit For
End If
End If
Next
End Function
Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
PostCode = UCase$(PostCode & " ")
Parts = Split(PostCode)
ValidatePostCode = (PostCode = "GIR 0AA " Or PostCode = "SAN TA1 " Or _
(Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _
(Parts(0) Like "[A-PR-UWYZ]#" Or _
Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _
Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _
Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
End Function