Excel Challenge #6

Discussion in 'Off-topic' started by Steve Hales, Jul 10, 2015.

  1. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    You've got Excel skills? Let's see.

    This month's challenge is in response to a genuine need. This isn't some academic exercise in convoluted Excel formulae, but a real problem looking for a real solution.

    Here are the usual rules.
    • Create a single Excel formula to solve the stated problem.
    • The shortest formula wins (when calculating the length of the formula there's no need to include the leading "=", or the curly braces that Excel uses to represent array formulae).
    • The formula's number format can be set to whatever you want.
    • Conditional formatting is not allowed.
    Problem #6

    Create a single formula to extract only the numerals from the contents of cell A1. For example, if A1 contains the string "1234ABC567", then the formula needs to return only 1234567.

    Have fun :)
     
    Steve Hales, Jul 10, 2015
    #1
  2. Simon James

    Simon James ActEd Tutor Staff Member

    Tough one - initial thought is nested substitute functions - but to strip out all non-numeric characters will require a lot of nesting!
     
    Simon James, Jul 13, 2015
    #2
  3. old.student

    old.student Member

    hmm

    cant claim this one as somebody else showed me - enter as array formula


    NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))



    magic of npv
     
    old.student, Jul 13, 2015
    #3
  4. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    That is simply outstanding :)

    I think there should be a special prize for using NPV!

    For those of you trying to figure how this works, the key to the magic can be found in the Excel help file:

     
    Steve Hales, Jul 13, 2015
    #4
  5. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    I sourced this array formula from someone else, but I've got no idea what's going on :confused:

    Code:
    =SUM(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(A1:A50),1))*
ROW(A1:A50),0),ROW(A1:A50))+1,1)*10^ROW(A1:A50)/10)
     
    Steve Hales, Jul 13, 2015
    #5
  6. Hemant Rupani

    Hemant Rupani Senior Member

    I try to read it inside out.:cool: :)

    --MID:- return numerical values and error for others.... Then,

    ISNUMBER:- return TRUE for Numbers, else FALSE. Then, multiplication with 'ROW' return digit places for TRUE and 0 for FALSE places.(we know TRUE=1,FALSE=0). we don't need INDEX.(formula without INDEX works same. as here, row_num=0). Then,

    LARGE:- arrange array in descending order. Then,

    0&A1:- is needed because, when we get 1st place for '0'(ROW(A1:A50))+1 in 1st MID), it won't give Text string. and return the number on the places from LARGE Then,

    10^ROW(A1:A50)/10:- to place the digits, already arranged in descending. ( unit, tens, hundreds.......). Then,

    SUM:- like, for afd2j3l3h return 3+3*10+2*100=233.
     
    Hemant Rupani, Jul 14, 2015
    #6
  7. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    I've been emailed a couple of improvements to the NPV solution. To overcome an issue with precision (eg the initial NPV solution gives "45678XX" --> 45678.0000000001) this is better:

    Code:
    =ROUND(NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,"")),0)
    In the interest of compactness, this will help:

    Code:
    =ROUND(NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(A:A),1)%,"")),0)
     
    Steve Hales, Jul 15, 2015
    #7
  8. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    Some pretty impressive Excel skills have been demonstrated here this month - well done. And a big round of applause to NPV who saved the day ;)

    Watch out for Excel Challenge #7 - launching Friday 14th August 2015.
     
    Steve Hales, Jul 20, 2015
    #8

Share This Page