+ Reply to Thread
Results 1 to 4 of 4

Explanation for formula that counts number of numeric characters

  1. #1
    Forum Contributor
    Join Date
    09-24-2012
    Location
    York, England
    MS-Off Ver
    Excel 2003/07/10
    Posts
    165

    Explanation for formula that counts number of numeric characters

    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

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Explanation for formula that counts number of numeric characters

    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"
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    09-24-2012
    Location
    York, England
    MS-Off Ver
    Excel 2003/07/10
    Posts
    165

    Re: Explanation for formula that counts number of numeric characters

    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

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Explanation for formula that counts number of numeric characters

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1