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

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)