Forum Discussion
Using TEXTSPLIT without copy-fill-down
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)- 4. I didn't consider the possibility that the target cell might be null. The TEXTSPLIT parameter will only address the situation of multiple consecutive separators within a populated cell. The #VALUE! error is a valid result which could be trapped if required.
- So that is interesting. Apparently if you set TEXTSPLIT to be case insensitive it must apply a LOWER() function to the output. I would NOT expect the output to be affected and only the comparator but if I change the TEXTSPLIT(q, "; ", , FALSE, 1, "") to TEXTSPLIT(q, "; ", , FALSE, 0, "") it solved that issue.
- One more question that I earlier missed:
3. What's the benefit using SplitTextλ - how would it impact if the code in SplitTextλ is included in SplitListtλ?- 3. It is just a style thing, though I admit I haven't applied it sufficiently to be able to recommend it. The idea is that all LAMBDA functions should be named, especially those accepting parameters from Lambda helper functions. The result is that the calling formula is more concise and dummy variables are not exposed.
I was looking through as you were refining the formula initially created by Matt. Two questions I have are:
1. In Matt's formula what was creating the lowercase?
2. What necessitates to include EXPAND with TEXTSPLIT? If we don't include EXPAND and instead provide all the parameters that TEXTSPLIT has, what would be the difference?
- 1. TEXTSPLIT has a case-sensitivity parameter that appears to have unexpected side effects.
2. EXPAND offers an alternative to trapping #N/A at the VSTACK stage
