joeu2004 wrote:
"David Farber" wrote:
Now my question is, which I have been unable to figure
out by experimentation, what is the correct result of:
SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") assuming
for example that A1 contains a city, state, and zip?
You said it would produce an array.

And it does. But if you simply array-enter (press ctrl+shift+Enter):

=SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")

into a single cell, Excel assumes you want to select a single element
of the array, the first element in this case.

You should have array-entered (press ctrl+shift+Enter):

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

It is MIN that knows what to do with the array.

If you want to see the array returned by array-entering that SEARCH
expression, use the Evaluate Formula operation. (It is available in
Excel 2003. I don't know about Excel 2002.)

Alternatively, select B1:K1 (a row of 10 cells, not a column), type
the following formula:

=SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")

then press ctrl+shift+Enter.

That was easy. (-; Worked like a charm. From there, I was able to save the
search value, I'll call it n, in a hidden column to make the subsequent
formulas much shorter. Then I used that value to construct another string
which started at the n-4th character (there are two spaces between state and
zipcode) and was two characters long for state. For the city, I started at
the beginning of the string and ended it at the n-5th character.

In case anyone is interested, this does work in Apache's Open Office Calc
except function arguments and array values are separated by semicolons, not
commas.

Thanks for your great help.

--
David Farber
Los Osos, CA