"David Farber" wrote:
In my original message I said, "...I tried doing the exact
same thing in Open Office Calc and it worked perfectly. "

Ah, yes. I went looking for such a reference before posting, but I missed
it.

To be clear: you are indeed asking about Microsoft Excel.

I'm not sure what the mystery still is.

I pointed to the subtle instructions in the ROW help page. You can enter
=ROW(C4:D6) into C4 (although =ROW(C4:C6) makes more sense for your
purposes), then select C4:C6, press F2, then press ctrl+shift+Enter.

Or with forethought, you can select C4:C46, type =ROW(C4:D6), then press
ctrl+shift+Enter.

As I noted before, I suspect you simply typed =ROW(C4:D6) in C4, then
pressed ctrl+shift+Enter. Of course, at that point, Excel does not know how
many rows to propagate the array formula into. That is why you see only 4
in C4.

I would be surprised the Apache Open Office spreadsheet (whatever it is
called) knows any better either, when you make the same mistake. But
anything is possible in software.

Be that as it may, I don't see how this exercise (and unnecessary way to
accomplish the same result with ROW per se) helps with your original problem
or curiosity. To wit....


"David Farber" wrote elsewhere (edited):
I found this link with this formula at :
=MID(A5,SEARCH(",",A1)+2,MIN(SEARCH({0,1,2,3,4,5,6 ,7,8,9},A1&"0123456789"))
-2-(SEARCH(",",A1)+1))....confirmed with CONTROL+SHIFT+ENTER.
I was curious to know how that expression was evaluated

Since SEARCH takes only a single-valued expression for the first parameter,
array-entering the formula cause the evaluation of the 2nd SEARCH expression
to create an array. In effect, it is as you had written (if we could, which
we cannot):

MIN({SEARCH(0,A1&"0123456789"),SEARCH(1,A1&"012345 6789"),...,SEARCH(9,A1&"0123456789")})

By appending "0123456789", the SEARCH will always succeed. That is needed
so that MIN does not propagate the Excel error that SEARCH would return if
it failed.

Thus, MIN sees an array of indexes (character positions) in A1&"0123456789".
By returning the smallest such index, MIN returns the position of the first
number in A1, if any.

Caveat: Microsoft Excel is inconsistent with where it permits the use such
"array expressions". For example, SUMPRODUCT(VLOOKUP(A1:A10,Table,2))
should be evaluated as if we had written
SUMPRODUCT({VLOOKUP(A1,Table,2),VLOOKUP(A2,Table,2 ),...,VLOOKUP(A10,Table,2)),
returning the sum of the 10 lookup results. But it does not :-(.

The point is: Always test any array-entered formula to be sure it is doing
what you intended. And avoid them when you can.