Given the list in A1:A6, where some entries consist of a single number and some of a grouping of numbers (where e.g. 13-16 represents 13, 14, 15 and 16), the challenge this week is to come up with a single formula in D1 which, when copied down an arbitrary number of rows, produces a list of all individual, ungrouped numbers from the list in A1:A6, as here:
The formula is to return a blank in rows beyond the expected number of returns. What’s more, this must be done via reference to a second formula, in C1, also to be derived and whose value is to equal the total expected number of non-blank entries to be returned in column D for any given dataset.
As such, the formula in D1 is to begin with:
(We don’t do “lazy” IFERROR approaches here at EXCELXOR!)
Readers whose first idea might be to develop a solution which first tackles the string in A1, then the string in A2, and so on until the string in A6 might want to bear in mind that I only chose a range of six cells here for display purposes. What I mean by that is that your solution should be able to cope equally if the data range was not A1:A6 but e.g. A1:A100.
It can be assumed that none of the cells in the range A1:A6 are empty.
As an additional caveat this week, readers are asked, where a solution contains one or more clauses which occur more than once within the formula, and which are of a sizeable length, to store these clauses as Named Ranges and reference those in the formula instead. Furthermore, the range A1:A6 is to be referenced as the Named Range Range1 in any solution.
Best of luck! Solution next week!