Searching for a number within a text string

This part does most of the work:

MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))

it basically replaces each digit it finds in the string with a 0, so you will get a result of 9 variations of the text string.. It then adds a 0 to the end of each element so that the Find() function doesn't return an error...

The Find() functions then finds the 0 in each of the elements and reports the position of the first 0 in each element..

The Min() returns the smallest position location which would translate to the earliest point that the Substitute function replaced a digit with a 0.. which is, of course, the first time a digit occurs in the original string....

This Min postion is then subtracted from the Length of the original string and 1 is added to determine how far from the Right to extract from.

This returns a text version of your number... if you want to convert to an actual number add a +0 to the end of the formula.

Note: To follow the evaluation go to Tools|Formula Auditing|Evaluate Formula and keep clicking Evaluate to follow the sequence of events leading to the final result.