How to extract only cells which contain a letter AND a number in Excel?

Question

I have a series of addresses from which I need to extract postal codes.The data is very sloppily formatted (not separated, some with spacings some without etc..) meaning the only way I can think to extract the postcodes is to create a column to which is added only the values which contain Text and a Number as these are the only cells to contain the postal code.

The data is too messy to isolate exactly where the postcode lies but I would like something to return a result like above.

How could I return only cell O2 & P2 from the range K2:R2?

*Address here is made up

Answer 1

For a VBA result you could use the code below.
In cell T2 enter =GetPostCode(K2:R2),
or in VBA you can use Debug.Print GetPostCode(Sheet1.Range("K2:N2"))

I can't remember where I got the pattern from, but can probably be improved.

Public Function GetPostCode(AddressRange As Range) As Variant

    Dim rCell As Range
    Dim sAddressString As String

    For Each rCell In AddressRange
        sAddressString = sAddressString & " " & rCell.Value
    Next rCell
    sAddressString = Trim(sAddressString)

    GetPostCode = ValidatePostCode(sAddressString)

End Function

Public Function ValidatePostCode(strData As String) As Variant

    Dim RE As Object, REMatches As Object

    Dim UKPostCode As String

    'Pattern could probably be improved.
    UKPostCode = "(?:(?: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})"

    Set RE = CreateObject("VBScript.RegExp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = UKPostCode
    End With

    Set REMatches = RE.Execute(strData)
    If REMatches.Count = 0 Then
        ValidatePostCode = CVErr(xlErrValue)
    Else
        ValidatePostCode = REMatches(0)
    End If

End Function

Edit: I thought it wasn't working as it only return E17 3RU which is in Walthamstow, but HE17 3RU isn't a valid postcode (http://www.royalmail.com/find-a-postcode) so it found the valid one.

Answer 2

Though I believe that @DarrenBartrup-Cook has a better answer. This quick dirty little UDF will do it bassed on the mix of numbers and text like asked.

Function pcode(rng As Range)
Dim rngt As Range
Dim chr As String
Dim i As Integer

For Each rngt In rng
    If Not IsNumeric(rngt) Then
        For i = 1 To Len(rngt)
            If IsNumeric(Mid(rngt, i, 1)) Then
                pcode = Trim(pcode & " " & rngt.Value)
                Exit For
            End If
        Next i
    End If
Next rngt
End Function

Put this in a module attached to the workbook, NOT the worksheet code or ThisWorkbook code.

You would call it from the sheet with this formula:

=pcode(I5:P5)