awesome!!!!
thank you very very much ... though i still don't quite understand the formula
Explaining one's own invention (a few years back though) should be possible...
Given that:
(a) FIND (also SEARCH) yields the start position of the search string within the target string. If the search string is not part of the target string, we get a #VALUE! error.
(b) MIN returns the first error value it encounters if any.
(c) FIND is given the set of digits to look for, i.e., {0,1,2,3,4,5,6,7,8,9}, in the target string to which a string of all digits appended. The latter in order to prevent #VALUE! errors.
If we evaluate
MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1
for the target string of A1, we get successively:
MIN(FIND({0,1,2,3,4,5,6,7,8,9},"A10123456789"))-1
MIN({3,2,5,6,7,8,9,10,11,12})-1)
where digit-0 at position 3, digit-1 at position 2, digit-2 at position 5, etc.
2-1
1, which is given to LEFT as the number of chars parameter.
Note. The formula sghould be:
=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1))
The earlier version has one paren less, which makes it unnecessarily calculation-intensive.
