I've just re-read my post, and ... to me ... I really don't see anything
that I believe would make you feel that I was taking your question as
anything but ... an honest question.
I did not say that you didn't take it as "honest question". But your
response was loaded with *emphasized* [sic] words and exclamation
marks! [sic] -- even one triple exclamation!!! [sic] If that was not
intended to convey a particular tone, I guess we have to take your
word for it. No matter. It did not bother me. I just wanted you to
settle down.
As to the question at hand, I don't see where Min() has anything to do with
the elimination of the error.
I don't believe I said it did. That's the problem: You focus on my
off-hand mention of #VALUE in some cases of FIND(), whereas my
question was about getting the right solution -- specifically, about
sequencing through the __array__ of constants "a", "b", etc.
So let's do a reset, and let me try to clarify one last time.
(For the OP, I want to note that this digression has nothing to do
with your problem and its solution. RagDyer provided a great solution
in his first posting.)
Yes, your concatenation of " a...z" is the ingenious key to avoiding
#VALUE from FIND() in all cases. That obviates the need to use IF()
as I did, which is very messy, not to mention grossly inefficient due
to the multiple FINDs.
As I said, great solution! Are we square on that? That should be the
end of any discussion about avoiding #VALUE.
My follow-up point was: Concatenating " a...z" in FIND() alone does
not result in the correct answer. That is, even though
FIND({"a",...,"z"},A1&" a...z") returns a numerical result instead of
#VALUE when the first mixed-case word in A1 is History, for example,
it returns the wrong numerical result, namely the position of "a" in
the concatenated part.
Enclosing that with MIN() -- that is, MIN(FIND({"a",...,"z"},A1&"
a...z")) -- is required in order to find "i" in History instead of "a"
in " a...z". That is, it is the enclosing with MIN() that causes
FIND({"a",...,"z"},...) to be evaluated as
{FIND("a",...),...,FIND("z",...)} (stylistically).
I took it for granted that you knew that. After all, why else did
your write MIN(FIND(...))? (Rhetorical.) Forgive me if I was
presumptuous. Perhaps you are only parroting a solution that someone
else crafted, and you never stopped to understand all of the elements
that make it work.
In any case, my original question was not "how does that work?" (or
"why does that eliminate #VALUE?"), referring to your solution. I
knew that from the start -- all of the elements.
My original question (now rhetorical because I believe I have answered
it for myself) was: Why does Excel recognize that as an array formula
without the need to commit with ctrl+shift+Enter (CSE), whereas CSE is
needed for Excel to recognize other forms of array formulas, for
example MIN(FIND(CHAR(CODE("a")+ROW($1:$26)-1),A1&" a...z")?
To reiterate: My question was about the handling of the array, not
about the #VALUE. I mentioned the #VALUE only incidentally because
that is the most obvious proof that FIND() itself is not sequencing
through the array {"a",...,"z"}. If we concatenate " a...z", yes,
FIND() does not result in #VALUE; but the number returned may be the
position of "a" -- and no other letter -- in the concatenated part.
(Unless there is an "a" -- and no other letter -- to the left.)
So Find() *is* calculating through the *entire* array constant.
No it is not. Please use Evaluate Formula to see what happens when we
have simply:
=find({"a",...,"z"},A1&" a...z")
where A1 contains "BLAH BLAH History". You will see that
{"a",...,"z"} is reduced to "a". Consequently, you will see that
FIND() returns 19, the position of "a" in the concatenated part,
instead of 12, the position of "i" in History.
This of course causes the error to be returned if it exists *anywhere*
within the array.
Yes, MIN(FIND(...)) returns an error if any of FIND("a",...),
FIND("b",...) etc fail. I don't believe I said or implied
differently. At least, that was not my intention.
*ONLY* the addition of the missing characters to the "Within Text" argument
will eliminate the error.
Yes. I did not intend to say otherwise. I wrote: ``So
FIND({"a",...,"z"},...) alone works only if "a" is in the second
argument (within_text). However, when MIN() is put around
FIND({...},...), as you did, Excel treats that as
MIN(FIND("a",...),FIND("b",...),...,FIND("z",...)).``
The operative words were "as you did" -- everything that you did. I
think the confusion arises, in part, because of my careless use of the
"..." shorthand without sufficient explanation. Mea culpa!
Or am I still not comprehending your elucidation?
Yes. But I hope this final posting (for me) helps your comprehension
of my points, which are different from your point about #VALUE.
If you still do not understand, no matter. We both know your solution
works, and I (at least) fully understand why -- I always did.
