Forum Discussion
Using TEXTSPLIT without copy-fill-down
I think the abject failure to process arrays or arrays is the most ridiculous error of judgement made by Microsoft in what is otherwise a suburb sequence of innovations, turning Excel from amateurs' corner into something capable of creating reusable, quality solutions. One of the principal benefits of the grid is that it allows one to calculate and display arrays of arrays. To pretend otherwise is to store up huge compatibility problems for when sense eventually prevails.
To offer an absurd solution, I developed a custom Lambda function "Repackλ" to turn the comma-deliminated values into fixed width strings
Repackλ
= LAMBDA(t,
LET(
splitText, TEXTSPLIT(t, ","),
length, LEN(splitText),
CONCAT(splitText & REPT(" ", 10 - length))
)
);
giving
What appears to be a monumental waste of effort pays off because MID will generate and array of arrays if used to wrap the Lambda function.
= MID( MAP(Text, Repackλ), {1,11,21,31,41}, 10)
giving
Having to go back to MID to achieve something that 'Excel has never done?' is so annoying that I propose to continue expressing my scorn at every opportunity (and there are many opportunities, which makes the need for change so urgent). I hope you all join in to make Microsoft listen even if just to save themselves further embarrassment!
dscheikey; mtarler; Patrick2788; PeterBartholomew1
Thanks to each of you for providing me with the exact help I was looking for on this forum.
dscheikey: Your formula might be an old school one but it did solve my problem. I created a similar formula but that was a copy-fill-down type - one formula per row which was restricting me to use the P3# spill-array addressing in subsequent processing. Many thanks for your help - I really liked the start and end formula snippets. The way I did it in my copy-fill-down formula was:
=LET(a, $B5, b, $C5, data, "|" & SUBSTITUTE(b, "; ", "|") & "|",
WrdCnt, LEN(data) - LEN(SUBSTITUTE(data, "|", "")),
start, FIND(CHAR(2), SUBSTITUTE(data, "|", CHAR(2), SEQUENCE(, WrdCnt))),end, SEQUENCE(1, COLUMNS(start) - 1),
O, MID(data, INDEX(start, end) + 1, INDEX(start, end + 1) - INDEX(start, end) - 1), HSTACK(a, O)),Seems, the way I had written the "end" was restricting the row-spill when extended to the complete range of rows. Thanks again.
mtarler: You provided the new school formula that I was looking for but was encountering errors due to the "array of arrays" issue. The formula with REDUCE, VSTACK, TEXTSPLIT did the breakthrough. However, there is still an issue which I am not able to resolve - the output generated by the formula is changing the case of the strings to lowercase. Please look through the "Solution - mtarler" worksheet in the attached. Do you see a reason why the case is getting changed and what is changing it?
https://1drv.ms/x/s!Aqyz05eCPz3-rWwakzdAFKiYsjtH?e=ydgpxa
Patrick2788: Thanks for sharing the link to the "Array of Arrays" limitation literature. I haven't had tried your formula on the actual problem (string length more than 32,767 characters) yet. However, looking at it, I see you have hardcoded the number of columns in the MAKEARRAY function parameters. Anyway, this is a small thing to rectify by taking the max of the col_delimiter count. Will let you know once I have looked through your formula.
PeterBartholomew1: You and Sergei are simply the Best. Though I can follow the solutions you provide, it would take quite some time for me to reach that level. I completely agree with you that going back to MID is really annoying after having seen the power of new age Excel functions.
I have 'interfered' with Matt's solution to add further refinement or complexity (however you take it) to create a worksheet function
= SplitListλ(Response,"; ",185)which uses the Lambda funtions
SplitListλ = LAMBDA(list,s,n, DROP( REDUCE("", list, SplitTextλ(s,n)), 1,0) ); SplitTextλ = LAMBDA(s,n, LAMBDA(p, q, VSTACK(p, EXPAND(TEXTSPLIT(q, s), , n, "") ) ));- Another issue I ran into:
4. If value in one of the response (Table3[Respose]) cells is made blank (delete the cell content), the formula generates a #VALUE! error - the error is not going away even after the value for the ignore_empty parameter of the TEXTSPILT function has been set to preserve (FALSE)
