If you would like to use a UDF (User Defined function); try the below. From
workbook launch VBE using Alt+F11. From menu Insert a Module and paste the
below function.Close and get back to workbook and try the below formula.

=FINDPC(A1)


Function FindPC(strData As String) As String
Dim intTemp As Integer, varTemp As Variant
If strData Like "* #### *" Then
For intTemp = 1 To Len(strData)
If Mid(" " & strData & " ", intTemp, 6) Like " #### " Then
varTemp = Trim(Mid(" " & strData & " ", intTemp, 6))
If CInt(varTemp) = 600 And CInt(varTemp) <= 9990 Then _
FindPC = CStr(varTemp): Exit Function
End If
Next
End If
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

If post code is the only numeric in the address try the below formula and
feedback. Please note that this is an array formula. Within the cell in edit
mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

With your address text in cell A1 try the below formula B1(array entered)

A1 = cross road,town,county, 2600

=IF(AND(COUNT(1*MID(MID(A1,MIN(SEARCH({0,1,2,3,4,5 ,6,7,8,9},A1&"0123456789")),99),ROW(1:99),1))=4,--MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(1:99),1)), 0),4)=600,--MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(1:99),1)), 0),4)<=9990),MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,R OW(1:99),1)),0),4),"")

If this post helps click Yes
---------------
Jacob Skaria


"Sooz" wrote:

I want a formula that will search a cell and identify whether that cell
contains a postcode or not - and return that postcode. A postcode is
identified as being a 4 digit number between 0600 and 9990.

Ideally it won't matter where in the text the postcode appears. The cell
will be a text (address) field.