Explanation for formula that counts number of numeric characters

**Hercules1946** · 11-21-2012, 02:42 PM

The formula below was posted recently as a way to extract a numeric substring from somewhere in larger string.
When I first saw this formula, I had no idea how it "came up with the goods" and so I spent some time looking at
parts of it to see what they did.
Although I've made some progress, I still don't fully understand it, and I would be grateful if someone could
enlighten me. Heres the formula, and below Ive detailed how far Ive got:

=Iferror(lookup(9.99e+307,--mid(a1,min(find({1,2,3,4,5,6,7,8,9,0},a1&1234567890)),row(indirect("1:"&len(a1))))),0)

I hope my findings below are correct:
1. The formula is returning the first substring of contiguous numeric characters from the full string in Cell A1
2. 9.99e + 307 ensures that the search value won't be exceeded by the returned value.
3. The Mid function uses Min(Find( to get the character position of the first numeric in A1 (so MID then has its first 2 parameters)
So far so good.
4. Now... I cant work out where MID parameter 3 (the substring length) comes from. I suspect its the row function, but can't see how
as this isn't an array formula, and without CTRL+Shift+ Enter, I could only get Row to return 1 in my experiments (giving me only the first substring digit).

How does the formula return the correct number of numeric characters, (apparently) without counting them?

Thanks for any help

**tigeravatar** · 11-21-2012, 03:23 PM

That's what this part does, this is the third argument of the Mid function:
row(indirect("1:"&len(a1)))))

Len(a1) returns a number that is how many characters exist in A1
Indirect puts that number together with "1:" to get a range reference. So if there are 12 characters in A1, it would return Indirect("1:12")
Row() takes that range reference and converts it to an array of values, so Row(1:12) = {1,2,3,4,5,6,7,8,9,10,11,12}

Mid gets evaluated with each of those being the third argument. So it starts at the first numeric character found, then sees if length 1 is a number, then sees if length 2 is a number, then sees if length 3 is a number, and so on until it sees if length 12 is a number

Lookup is trying to find the largest number Excel can handle, 9.99e+307. If Lookup can't find its target, it returns the largest value that is still smaller than its target. Lookup is also a function that ignores error values in arrays, which makes it particularly suited to this task. So it will return the number of length x, where x was the largest number returned by the mid function.

"x 321 test x"

The first numeric value is 3 at location 3
Mid then returns this array:
{"3","32","321","321 ","321 t","321 te","321 tes","321 test","321 test ","321test x"}

The -- is called a double unary. It is used to return a value as itself. 0+ or 1* can accomplish the same thing. What this does is convert numbers stored as text to numbers. This is necessary because the Mid function can only have a text output, even if the output is numbers. If it tries to convert an actual text value to a number, the operation will fail and a #VALUE! error will be returned. So now the array looks like this:

{3,32,321,321,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

Note that the quotation marks are gone and the numbers are now actual numbers instead of numbers stored as text. Any part in the array that had actual text is now an error. Now the Lookup function can return the largest value that is still below its target. It finds 321 to be that number so that is what is returned.

A useful tool that will also go through the steps of the formula is the Evaluate Formula tool. In Excel 2007+ select the cell with the formula, go to the Formulas tab, click on "Evaluate Formula"

**Hercules1946** · 11-21-2012, 05:12 PM

Hello Tigeravatar
Thanks for your response, which has cleared up all the points I didn't understand. Ill try the formula evaluate, which I wasn't aware of.
These formulae can be a bit difficult, as some of the processes can't be seen by breaking it down into smaller steps which is my
accustomed way of solving this type of problem. Your explanation was first class. Im very pleased to click your star!

regards

Hercules

**tigeravatar** · 11-21-2012, 05:16 PM

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save