Monthly Archives: April 2015

Shortest Formula Challenge #5: No Array Constants, Please 18

Shortest Formula Challenges • Tags:

I was recently helping someone to develop a formula-based Conditional Formatting rule, a stipulation for which (at least in 2010) is that the explicit use of array constants is forbidden.

Of course, we can get around this by first storing these array constants as Defined Names and then referencing those in the formula instead, which is perfectly legitimate.

On that particular day, however, I was in a rather creative mood (no doubt to the OP’s detriment!), and so went about the (somewhat convoluted) task of creating a version of my formula in which any array constants were replaced with suitable functional constructions, only to find that this process was not always quite as straightforward as I had first presumed…

More…

Extracting Numbers of Set Length Only from Alphanumeric Strings 18

Extracting Numbers from a String • Tags: , , , , , , , , , , , , ,

In this post I would like to present a solution to the practical problem of extracting a number of defined length from an alphanumeric string which may contain several numbers of varied lengths.

Indeed, the inspiration behind this post is in part derived from having personally witnessed many such requests on the various Excel forums, most of which involve the extraction of e.g. an account number of fixed length, 6 digits, say, from a longish string containing many other numbers.

As an example, given the following string:

20/04/15 - VAT Reg: 1234567: Please send 123456 against Order #98765, Customer Code A123XY, £125.00

we may wish to extract the one occurrence of a 6-digit number (123456) from that string.

More…

Shortest Formula Challenge #4: Results and Discussion 5

Shortest Formula Challenges • Tags: , , , , , ,

Last week I set readers the challenge which can be found here.

A good response to this one, leading to a solution for which, in the end, most people who responded can take some credit.

Snakehips started the ball rolling with a nice logical construction involving “OR”ing two separate COUNTIFs; John Jairo V then shaved off several characters from this solution; this was then further refined by Elias; and, finally, after several attempts at constructing a solution using FREQUENCY, Alex Groberman took the COUNTIF set-up and wrapped it in that most wonderful of functions – MODE.MULT – to give us our winner.

More…

Shortest Formula Challenge #4: Consecutive Integers 32

Shortest Formula Challenges • Tags:

The challenge this week is as follows: given the range A1:D5, in which each of the entries is an integer and is unique within that range, a single formula to generate an array consisting of all values which form part of a consecutive sequence of at least two entries from that range. The elements within this array are also to be returned in an order from smallest to largest.

For the example below:

Consecutive Integers

one possible answer would be (as highlighted):

{1;2;3;12;13;14;15;16;17;36;37}

More…