In summary, it looks for all the numeric values (and adds 0123456789 to the
end to ensure all are found), and then builds an array of all numeric
strings within the overall string (the MIN is used to find the first one,
and thus where to start). LOOKUP with a lookup value of
9.99999999999999E+307 (which is the largest number that can be stored in a
cell) if it can't find a value (which it shouldn't using
9.99999999999999E+307 <vbg>) will return the largest value in the array,
which will be the final string in this case.
Using an example of ABC123 in A1.
SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) will return an array of
{7,4,5,6,11,12,13,14,15,16} - not that these numbers relate to the position
in A1, but some are greater than 6 because we appended 0123456789
MIN will the return 4 - self explanatory
ROW(INDIRECT("1:"&LEN(A1))) returns an array of {1;2;3;4;5;6}which is passed
to the MID function to extract sub-strings
MID(A1, SEARCH(...),ROW(...)) then builds an array of numeric substrings
{"1";"12";"123";"123";"123";"123"} - note that we use the length of A1 in
case it is all numeric, but it does mean that the final numeric substring,
123, gets repeated 3 more times because of the 3 letters at the start
The -- is just used to transform the array to numeric values
LOOKUP then lookups 9.99999999999999E+307 in that array, doesn't find it, so
it returns the largest value found, 123
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
