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:
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:
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.)
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:
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:
Of course it is by far not that elegant as UDF solution. But shall work fine too
Bookmarks