Extracting UK post codes

Derek Stephen said:

How can I extract UK post codes from a non uniform text string into a separate row

Click to expand...

• The letters QVX are not used in the first position.
• The letters IJZ are not used in the second position.
• The only letters to appear in the third position are ABCDEFGHJKPSTUW when the structure starts with A9A.
• The only letters to appear in the fourth position are ABEHMNPRVWXY when the structure starts with AA9A.
• The final two letters do not use the letters CIKMOV, so as not to resemble digits or each other when hand-written.

Derek Stephen said:

Hi

Please see

4 examples below extracted from a database in Excel . What I need to do is extract the postcodes highlighted in yellow. Thank you for your response . Derek

Church Farm House, 6 Church Farm Lane, Sidlesham, Chichester, PO20 7RE - F/C Tank Model Major, Flat Top Blowdown Vessel, Sample Cooler, Mini Duplex Water Softener, Delivery & Installation


Supplied 2003 -Installed 41 Market Place, Reading, Berks RG1 2DE Branch Ref: 6524

Skid Assembly with 2 boilers + ancillaries Installed @ Holywell Hospital, 60 Steeple Road, Antrim
BT41 2RJ

Unit 7A Chaucer Business Park, Watery Lane, Kemsing, Sevenoaks, TN15 6PW - Ancillaries Blowdown Vessel, Model "Standard" S/S F&C Tank - Customer collected

Click to expand...

Snakehips said:

Here is some post code info that might help someone take up the challenge.

Post codes are two letter /number strings separated by a single space.

Formats.....

AA9A 9AA -- e.g.EC1A 1BB
A9A 9AA -- W1A 1HQ
A9 9AA -- M1 1AA
A99 9AA -- B33 8TH
AA9 9AA -- CR2 6XH
AA99 9AA -- DN55 1PT

• The letters QVX are not used in the first position.
• The letters IJZ are not used in the second position.
• The only letters to appear in the third position are ABCDEFGHJKPSTUW when the structure starts with A9A.
• The only letters to appear in the fourth position are ABEHMNPRVWXY when the structure starts with AA9A.
• The final two letters do not use the letters CIKMOV, so as not to resemble digits or each other when hand-written.

Good luck with this one. ** Edit Rick's awake so you might have a chance

Click to expand...

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