Lori,

Thank you very much - this is impressive!

Michael

"Lori Miller" wrote:

The original requirement stated:

"A postcode is identified as being a 4 digit number between 0600 and 9990"

The point of adding the extra parts to the formula was to make
it more robust**. My interpretation was that the formula should
only take such codes and exclude everything else, this means that
three digit numbers without a leading zero and parts of larger
numbers should be ignored. I would not agree that you should
treat such numbers as postcodes or return "999" from " 9991 ".

1&2) were added to restrict return values to these exact ranges
by checking that the surrounding characters are non-numeric.

Other numbers might occur in the house number for example.
Since the postcode is likely to be at the end of the address it
might be better to take the last match instead. You could do this
by replacing MAX([€¦],0) by LOOKUP(9999,1/1/[€¦]^0.5^2) in
the formula (this makes negative and zero values into errors.)

3) &" 0/1" was inserted to exclude any non-integer values from
the result as well as extra spaces by adding a zero fractional part.

If the address included text such as 12e2, 7e3, 1**3, these
should not be included. Other things to watch out for are items
that evaluate to dates or times which are in the numbers range
eg an appartment number 1/25 could evaluate to a 1900 date
depending on regional settings.

One more tweak to exclude possible negatives would be to
use --(0&TEXT(€¦)) instead of --TEXT(€¦)
____________
**If you want a simpler formula that does not account for 1&2 maybe try:
=LOOKUP(9999,FIND(ROW(600:9990),A1),ROW(600:9990))

"Michael R" wrote:

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.