For an address in A1, try entering this to return the postcode:

=MAX(MMULT({1,1,1},--TEXT(MID("."&A1&".",COLUMN(A:IV)
+{0;1;5},{1;4;1})&" 0/1","[9990]-9999;[=600]0;-9999;\0")),0)

If a postcode is not found in the address it returns 0.
If there were more than one postcode it returns the larger value.


"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.