• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

Excel Challenge #6

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 :)
 
Tough one - initial thought is nested substitute functions - but to strip out all non-numeric characters will require a lot of nesting!
 
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
 
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

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:

NPV function:

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
 
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)
 
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)

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.
 
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)
 
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.
 
Back
Top