On Tue, 18 Aug 2009 00:16:01 -0700, 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.

One way is with a UDF.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=PostCode(A1)

in some cell.


======================================
Option Explicit
Function PostCode(s As String) As String
Dim re As Object, mc As Object
Const sPat As String = "\b\d{4}\b"
Dim lPC As Long

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If re.test(s) = False Then Exit Function
Set mc = re.Execute(s)
lPC = Val(mc(0))
If lPC = 600 And lPC <= 9990 Then
PostCode = mc(0)
End If
End Function
===================================
--ron