Excel Challenge #6

[Steve Hales]

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

 

[Simon James]

Tough one - initial thought is nested substitute functions - but to strip out all non-numeric characters will require a lot of nesting!

 

[old.student]

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

 

[Steve Hales]

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.

 

[Steve Hales]

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

=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)

 

[Hemant Rupani]

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

=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.

--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.

 

[Steve Hales]

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:

=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:

=ROUND(NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(A:A),1)%,"")),0)

 

[Steve Hales]

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.