C
carl.manaster
Several years ago, in a post that seems now closed to replies, Ron
Rosenfeld posted a solution to someone's request for a formula that
found the location of the first digit in each cell:
A colleague wanted to know how to do this and I tracked down the
post. But it didn't satisfy, quite (though it works perfectly); I
wanted to understand it, too. So I deconstructed it and wrote it up
for my colleague, then figured I would post it here as well for
posterity. So here is my explanation of the above formula.
The expression
INDIRECT("1:"&LEN(A10))
identifies the rows between the first and the one whose number is the
length of the text in A10. Why in the world would we want that?
Well, when we put ROW() around it and put it into an array formula, it
produces a series of integers, because ROW() returns an integer. So
that's a tricky way to get (in this case) the series {1;2;3;4;5;6}.
The formula reduces to:
{=MATCH(TRUE,ISNUMBER(--MID(A10,{1;2;3;4;5;6},1)),0)}
That's starting to make more sense.
The MID() of A10, for each element in the series 1-6, one character
long - that's just identifying the individual characters in turn. And
that's why the LEN(A10) was used to determine the series length. So
we reduce it to
{=MATCH(TRUE,ISNUMBER(--{"a"; "b"; "c"; "1"; "2"; "3"}),0)}
in the specific case where A10 holds "abc123". What's that "--" all
about?
Well, ISNUMBER("1") is false. But -"1" is -1, whereas -"a" produces a
#VALUE error; the double minus simply restores the original sign
(which in fact is unnecessary in this case because ISNUMBER(-1) is
also TRUE).
We're getting there.
{=MATCH(TRUE,{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE},0)}
That says, find the first value in the series that matches TRUE and
return its position; the zero at the tail end I think signifies that
we require an exact match.
Got all that? This will be on the quiz...
Peace,
--Carl
Rosenfeld posted a solution to someone's request for a formula that
found the location of the first digit in each cell:
A colleague wanted to know how to do this and I tracked down the
post. But it didn't satisfy, quite (though it works perfectly); I
wanted to understand it, too. So I deconstructed it and wrote it up
for my colleague, then figured I would post it here as well for
posterity. So here is my explanation of the above formula.
The expression
INDIRECT("1:"&LEN(A10))
identifies the rows between the first and the one whose number is the
length of the text in A10. Why in the world would we want that?
Well, when we put ROW() around it and put it into an array formula, it
produces a series of integers, because ROW() returns an integer. So
that's a tricky way to get (in this case) the series {1;2;3;4;5;6}.
The formula reduces to:
{=MATCH(TRUE,ISNUMBER(--MID(A10,{1;2;3;4;5;6},1)),0)}
That's starting to make more sense.
The MID() of A10, for each element in the series 1-6, one character
long - that's just identifying the individual characters in turn. And
that's why the LEN(A10) was used to determine the series length. So
we reduce it to
{=MATCH(TRUE,ISNUMBER(--{"a"; "b"; "c"; "1"; "2"; "3"}),0)}
in the specific case where A10 holds "abc123". What's that "--" all
about?
Well, ISNUMBER("1") is false. But -"1" is -1, whereas -"a" produces a
#VALUE error; the double minus simply restores the original sign
(which in fact is unnecessary in this case because ISNUMBER(-1) is
also TRUE).
We're getting there.
{=MATCH(TRUE,{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE},0)}
That says, find the first value in the series that matches TRUE and
return its position; the zero at the tail end I think signifies that
we require an exact match.
Got all that? This will be on the quiz...
Peace,
--Carl