Finding A UK Post Code in a Random String

[welshraz]

I have a set of very annoying data that I need to pull the UK postcodes from. They look like this:

WOODBURY";"WOODBURY";"EX5 1EE";"(SDU)";"Parent Shell";"false";"0";"0";"0";"";"Other";"";"Safe";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.679902";"-3.391585"

EXETER";"WOODBURY SALTERTON";"EX5 1EL";"(SDU)";"Commercial";"false";"1";"0";"0";"";"Other";"";"Contract";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.697388";"-3.386321"

BARNSTAPLE";"BARNSTAPLE";"EX32 9LP";"SDU";"Residential";"true";"0";"1";"0";"D";"Other";"Future";"Unknown";"NORTHMOOR";"";"";"";"";";"";"false";"false";"false";"false";"";"1313zobYAAS";"view";"51.070795";"-4.033579"

The post code is not always in the same position, and not always in the same column of data. I was using a combo of TEXTBEFORE and TEXTAFTER, using the space as the delimiter, but some town names have more that one word in their name, so that is a bust.

Any help greatly appreciated.

 

[Fluff]

If it's always after the 2nd ; how about

Fluff.xlsm
	A	B
1
2	WOODBURY";"WOODBURY";"EX5 1EE";"(SDU)";"Parent Shell";"false";"0";"0";"0";"";"Other";"";"Safe";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.679902";"-3.391585"	EX5 1EE
3	EXETER";"WOODBURY SALTERTON";"EX5 1EL";"(SDU)";"Commercial";"false";"1";"0";"0";"";"Other";"";"Contract";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.697388";"-3.386321"	EX5 1EL
4	BARNSTAPLE";"BARNSTAPLE";"EX32 9LP";"SDU";"Residential";"true";"0";"1";"0";"D";"Other";"Future";"Unknown";"NORTHMOOR";"";"";"";"";";"";"false";"false";"false";"false";"";"1313zobYAAS";"view";"51.070795";"-4.033579"	EX32 9LP
Sheet6

Cell Formulas
Range	Formula
B2:B4	B2	=CHOOSECOLS(TEXTSPLIT(SUBSTITUTE(A2,CHAR(34),""),";"),3)

 

[Georgiboy]

Here is a UDF I have used in the past, I have amended it to work with your data. It should find the postcode wherever it may be. This could also be done with REGEX as well I would imagine, but I already had this code saved in my code snippet folder.

Function UKPostcode(InpStr As String)
    Dim w As String, Ptrn2 As String
    Dim j As Long, i As Long
    Dim Ptrn1 As Variant, x As Variant
 
    x = Split(Replace(Replace(InpStr, """", ""), " ", ";"), ";")
   
    '                L    N        L    N    N        L    L    N        L    L    N    N
    Ptrn1 = Array("[A-Z][0-9]", "[A-Z][0-9][0-9]", "[A-Z][A-Z][0-9]", "[A-Z][A-Z][0-9][0-9]")
    Ptrn2 = "[0-9]*" ' Number & something
   
    On Error Resume Next
    For i = 0 To UBound(x)
        w = x(i)
        For j = LBound(Ptrn1) To UBound(Ptrn1)
            If Len(w) Then
                If w Like Ptrn1(j) And x(i + 1) Like Ptrn2 Then
                    If Err.Number <> 0 Then
                        Err.Clear: If w Like Ptrn1(j) & Ptrn2 Then UKPostcode = w: Exit Function
                    Else
                        UKPostcode = w & Space(1) & x(i + 1): Exit Function
                    End If
                ElseIf w Like Ptrn1(j) Then
                    UKPostcode = w: Exit Function
                End If
            End If
        Next j
    Next i
    On Error GoTo 0
End Function

Used in the sheet as below:

Book1
	A	B
1	WOODBURY";"WOODBURY";"EX5 1EE";"(SDU)";"Parent Shell";"false";"0";"0";"0";"";"Other";"";"Safe";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.679902";"-3.391585"	EX5 1EE
2	EXETER";"WOODBURY SALTERTON";"EX5 1EL";"(SDU)";"Commercial";"false";"1";"0";"0";"";"Other";"";"Contract";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.697388";"-3.386321"	EX5 1EL
3	BARNSTAPLE";"BARNSTAPLE";"EX32 9LP";"SDU";"Residential";"true";"0";"1";"0";"D";"Other";"Future";"Unknown";"NORTHMOOR";"";"";"";"";";"";"false";"false";"false";"false";"";"1313zobYAAS";"view";"51.070795";"-4.033579"	EX32 9LP
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=UKPostcode(A1)

 

[welshraz]

Here is a UDF I have used in the past, I have amended it to work with your data. It should find the postcode wherever it may be. This could also be done with REGEX as well I would imagine, but I already had this code saved in my code snippet folder.

Function UKPostcode(InpStr As String)
    Dim w As String, Ptrn2 As String
    Dim j As Long, i As Long
    Dim Ptrn1 As Variant, x As Variant
 
    x = Split(Replace(Replace(InpStr, """", ""), " ", ";"), ";")
  
    '                L    N        L    N    N        L    L    N        L    L    N    N
    Ptrn1 = Array("[A-Z][0-9]", "[A-Z][0-9][0-9]", "[A-Z][A-Z][0-9]", "[A-Z][A-Z][0-9][0-9]")
    Ptrn2 = "[0-9]*" ' Number & something
  
    On Error Resume Next
    For i = 0 To UBound(x)
        w = x(i)
        For j = LBound(Ptrn1) To UBound(Ptrn1)
            If Len(w) Then
                If w Like Ptrn1(j) And x(i + 1) Like Ptrn2 Then
                    If Err.Number <> 0 Then
                        Err.Clear: If w Like Ptrn1(j) & Ptrn2 Then UKPostcode = w: Exit Function
                    Else
                        UKPostcode = w & Space(1) & x(i + 1): Exit Function
                    End If
                ElseIf w Like Ptrn1(j) Then
                    UKPostcode = w: Exit Function
                End If
            End If
        Next j
    Next i
    On Error GoTo 0
End Function

Used in the sheet as below:

Book1
	A	B
1	WOODBURY";"WOODBURY";"EX5 1EE";"(SDU)";"Parent Shell";"false";"0";"0";"0";"";"Other";"";"Safe";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.679902";"-3.391585"	EX5 1EE
2	EXETER";"WOODBURY SALTERTON";"EX5 1EL";"(SDU)";"Commercial";"false";"1";"0";"0";"";"Other";"";"Contract";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"false";"false";"false";"false";"";"";"";"50.697388";"-3.386321"	EX5 1EL
3	BARNSTAPLE";"BARNSTAPLE";"EX32 9LP";"SDU";"Residential";"true";"0";"1";"0";"D";"Other";"Future";"Unknown";"NORTHMOOR";"";"";"";"";";"";"false";"false";"false";"false";"";"1313zobYAAS";"view";"51.070795";"-4.033579"	EX32 9LP
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=UKPostcode(A1)

This is *chefs kiss*!! Thank you so much

 

[Georgiboy]

You're welcome, thanks for the feedback.