Lori,
This formula is a complex beast - but it fascinates me since it (almost)
works and that even without VBA.

In your fomula there are a number of items that I don't understand why they
are the
1) the "." before and after A1
2) the 3 arrays {1,1,1}, {0,1,5}, {1,4,1}
3) the &" 0/1"

So I stripped them out leaving a simplified formula of
=MAX(MMULT(1,--TEXT(MID(A1,COLUMN(A:IV),4),"[9990]-9999;[=600]0;-9999;\0")),0)

.... and much to my surprise it works too; it works even better because in my
test string of
wuwu123456wuwu
.... containing 3 4 digit strings (1234, 2345, 3456)
your formula returns a 0, mine returns 3456.


Can you (or anybody else) enlighten me what the idea of the above mentioned
seemingly unnecessary features is?

"Lori Miller" wrote:

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.