Expanding Number Ranges in String; Improving Upon a Nasty Solution

[Stever7]

So I have a problem that ties in with one I posted (and had fantastically resolved here). The problem is such that;

• I have strings that contain numbers (integers); these numbers may be given as individual numbers (e.g. 3, 5, 7), as ranges (e.g. 3-6) or combinations thereof
• The input string will be delimited by commas and may contain spaces (of various lengths; I'm just assuming input mistakes here!)
• The output string is to be delimited by pound/hash/number symbols (e.g. '#')
• There needs to be a way to take a special input of "All" and convert that to an arbitrary output of lots of numbers in a pound/hash/number symbol delimited string, so that it will work in the problem mentioned/linked to at the start of this post; or else an idea of how to modify that problem to work without this arbitrary long list of numbers
• I'm currently working on the idea that 1-18 would be more than enough in all likelihood, but solutions that can be extended further (up to say 30ish) would be preferable
• I'm currently saying 3 ranges is beyond the maximum that I would expect, so being able to process 3 ranges is sufficient; but methods that could do any number or ranges would be an amazing addition

I have already solved this problem to some extent (see the attached workbook), however my solution is horrific. To cope with up to 3 ranges, it requires ~15 helper cells, or else if I roll it all up to get it down to 1 cell per 'pass' (each pass expanding up to 1 range), then it becomes stupidly complicated, etc. (taking up around 1.5 a4 pages!!!). So, as those late night adverts say "There has to be a better way"; and as such I'm posting to see if someone is able to simplify the solution to this problem. The one limitation (off the top of my head) being no VBA or macros; as with the previous problem I posted, I have nothing against these methods in general but for what I'm doing, it would force me to jump through some admin hoops I'm hoping to avoid for as long as possible!

I've left a space for the new method(s) in the workbook I've attached, but feel free to work in a new sheet, etc. if that's easier. The main worksheet also contains 10 test cases to help check if the potential solutions work as expected

If any more information is required, etc. please just ask

[Kaper]

If you can accept working with macro-enabled workbook, (I know - you want to avoid this, but in my opinion - the potential gain is well worth trying) then hassle-free solution would be using UDF (user defined function - VBA code acting like built-in function).
So the firmula in cell could be:

Please Login or Register  to view this content.

and the code in a workbook:

Please Login or Register  to view this content.

To see (or modify) code press Alt+F11 to see it in Visual BAsic Editor.

Just a reminder - you have to enable macros - otherwise it will not work. the output (formula generating output) is currently in Q1 so you can compare it with "15 helper cells method" but of course the formula "looks" only in contents of M2 cell - or any specified argument - so all cells in rows 3- could be deleted).
By the way - handling of such cases as above used ...,3-,... could be easily added to the cody by:
changing:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Error handling (somebody writing 4-B instead of 4-8) etc. could be also added if needed. And all the time from the point of view of enduser it is just like standard formula.

[Stever7]

Hi Kaper,

Firstly thanks for the help That's a very eloquent method and I'm sure I'll incorporate that going forward but I am keen to keep it macro free for now just to delay the admin hoops I'll have to jump through. If possible I'd like to produce a macro-free file that can serve as a bit of a "not ideal but this is a starting point" document, and then in the near future produce a "using macros it can be improved like this..." type of document. I'll make a note of this method and put it into the second of these documents, but yeah for now if there's a macro-free method that improves upon the mess I've somehow made work, then I'd prefer to stay on the macro-free path for a little bit longer

[Kaper]

It took me some time, but as your sample file was that well prepared (I shall commit, it's rare on the forum), it was a pleasure to work and test on this file.
So I did the following, and here is my "only 5 formulas - of this 4 very simple" solution (cells count is higher).

0) Probably you need more such transformations, so I placed all helper cells in the same row - you can then copy the formulas down to use in next row. Also this approach allows for simple copying. but of course you can insert first formula in one row, then each set of 3 in next, and next, and ...

1) Let's start with "clearing our input" in S2:

Please Login or Register  to view this content.

So extra spaces are removed and "All" is replaced by desired range.

2) Then in T2 we will identify first component (could be single number or range). If it is not last part, it will be ended by coma. If last - no comma found:

Please Login or Register  to view this content.

3) In U2 we have the main formula. It is array formula (confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.)

Please Login or Register  to view this content.

if there is no - in the part in previous cell (T2) it wil simply substitute comma with #, as you did before. But if there is a range it will be changed into chain of # connected numbers - I used new function textjoin, but you declared excel 2016 in your profile) also had to use indirect to transform text like "13:17" (obtained by substitute function) into rows numbers 13:17.
Why text from empty (at this stage) cell X2 is added - see below *

4) Then in next cell (V2) we have remaining part of our input text obtained by simple formula:

Please Login or Register  to view this content.

And now (if there were some comma separated values/ranges), you can see that in V2 we have similar situation as we had a moment ago in S2. So we can copy all 3 formulas from T2:V2 to W2:Y2, and into next 3 cells, and as many times as we expect maximum number of commas in our input text.
*) So X2 contained converted second part, or as a matter of fact converted second and each following part, because it is converted second part and what was in a cell AA2 (third part abd the further parts). Of course as we use all parts then next remaining part is alvays empty so it is also converted to empty one.

5) The final touch is then in Q2 and it is again simple formula:

Please Login or Register  to view this content.

Of course it is by far not that elegant as UDF solution. But shall work fine too

[Stever7]

Hi Kaper,

Thank you for your kind words about the test case provided; my view is very much that if people are putting in time to help me then the least I can do is try and make it as painless as possible for them!

Annoyingly whilst from a glance your solution does look like a giant step in the right direction, I'm unable to use it Whilst I do have Excel 2016, it appears textjoin is only available on 2016 if you have a 365 subscription (which I don't). As such, as soon as I make the document 'live' all of the partial results break (Really sorry that this is the case! I'll update the version in my profile to reflect this limitation)

[Stever7]

I've been continue to work on this problem and, whilst it's still not exactly streamline, I've greatly reduced the size of the rolled up version (from ~1.5 A4 pages to <0.2 A4 pages + 1 helper cell [total, not per pass]).

I'm getting towards the point of being happy with it, but will continue to see if I can spot room for improvement; and as always, any ideas anyone has would be greatly appreciated.

P.S. In the document I've changed the format on the "#" delimited string slightly to include spaces as it doesn't impact on the end use and allowed me to do a couple things I don't think I could have before. I've also extended the string of numbers to 20 for the new method as 18 was annoying me :P