See inline comments...
I can use this constantly - if I understand it
Before you go and use this everywhere, remember that it was designed for
your specific case... in effect, find the first digit that is not a zero
(notice that the array and listing of digits do not have a zero in them),
which is also why I said this formula only applies IF the first character
after the leading zeroes is a digit (it will fail to work correctly if a
non-digit ever follows the leading zeroes).
and Gosh I hate to ask but could you explain how it works -
I'll try.
I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe
it uses the &"123456789" constant
but I am really confused as to the MIN function and the 99 value and
just basically how the formula flows.
I guess the best place to start is by giving an overview of what the formula
does. It finds the location of the first non-zero digit within the text and
uses that to find the starting point for pulling out the text you wanted. It
uses the MID function to get pull out a sub-string from the main piece of
text. The format of the MID function is...
MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint)
We will describe how to get the StartingPoint (the location of the first
non-zero) in a moment; but, once you have it, you wanted that digit along
with the remainder of the text after it. We don't know how many characters
that will be as the number of leading zeroes can vary; however, there is no
problem in asking for more characters than exist, so I took a guess that
your text will never be longer than 99 characters (which is why the last
value is 99... it is the 3rd argument of the MID function). Okay, so how do
we get the StartingPoint. We use ths code...
MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))
And how does that work. First off, we note that the FIND function cannot
process an array (values contained within curly braces); however, the MIN
can and so, embedding an array or a function call with an array (even if
that function can't normally handle arrays) within it forces the array to
get processed. This happens be feeding each array element into its encasing
function (the FIND function in this case) one at a time so that the MIN
function can determine which evaluated value is the smallest. So, the MIN
function is forcing these evaluations to take place...
FIND(1,A1&"123456789")
FIND(1,A1&"123456789")
FIND(1,A1&"123456789")
FIND(1,A1&"123456789")
