Explanation Needed - Extracting Numbers from a String

That is quite an impressive formula. I would never have thought of that in a million years.

SUMPRODUCT acts as a way to iterate through ranges and compute the product of corresponding elements in the ranges. For example, if you use a formula like this:

Formula:

=SUMPRODUCT(A1:A3, B1:B3)

the result is A1*B1 + A2*B2 + A3*B3.

In this case, the range is a series of rows, starting at row 1 and continuing to the row that is identified as the number of characters in the string in A5.

Formula:

INDIRECT("1:"&LEN(A5))

The tricky part here is that we don't really care about rows, but we care about the row numbers of those rows--it's a clever way to force SUMPRODUCT to iterate through every character in A5. For each character, if it is a digit (ISNUMBER), it multiplies that digit times the power of 10 corresponding to its position, then sums all those products up. I haven't quite sorted out how it determines the power of 10 to use, but that is the principle in play here.

@ DavidFC

It has always been my understanding that this formula was created by Harlan Grove several years ago.

This was like seeing an old friend again. So thanks for posting. Like you I (self taught) recall spending many happy hours taking this apart and putting it back together in various ways so I could learn array formulas. I nearly wore out the F9 key and Evaluate Formula (Fx) doing so. LOL

So keep "plugging away". The time spent in understanding this one is well worth the effort.

First, I would point out that the formula posted in Message #1 does more than the website that was linked to shows. That formula will return all digits in a cell; for example for this...

ABC1DE23FGH456

it will return this...

123456

As for the OP's search, if there is always a space after the number being searched for (as in the examples at the website you provided a link for), then this shorter normally-entered formula could be used...

Formula:

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),FIND(" ",A1&" ",10)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))