Reply to thread

Message: [QUOTE="carl.manaster, post: 3703832"] 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 [/QUOTE]

Verification