Forum Discussion
How to split cell containing FRACTION into two cells including Numerator and denominator
- Apr 20, 2022
Try this.
=TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(TEXT(B8,"# ????/????"),"/","#"),"#","</z><z>")&"</z></y>","//z"))
PeterBartholomew1 I hear you! As mentioned, the TEXTSPLIT function is not capable of spilling its results with an array of values, unless implementing some sort of workaround. TEXTJOIN works but is limited to 32,767 characters. In this case, with each text string containing 9 characters + 1 delimiter, if the dataset contained more than 3,276 rows of data, TEXTJOIN would fail with the #CALC! error.
The only other workaround I've seen for creating an array of arrays is to use the REDUCE function with VSTACK. For example:
=REDUCE({"Numerator","Denominator"}, A2:A10000, LAMBDA(v,n,
VSTACK(v, --TEXTSPLIT(TEXT(n, "????/????"), "/"))))
However, this method performs very poorly with large datasets, due to its iterative nature and repeated use of VSTACK for each row in the range. If the dataset is large enough, it may even cause Excel to crash.
Your first example, though, using TEXTBEFORE and TEXTAFTER with HSTACK is lightning-fast in comparison when used with large datasets, and is definitely the way to go. My take would be to use something like this:
=LET(
arr, TEXT(A2:A10000, "????/????"),
--HSTACK(TEXTBEFORE(arr, "/"), TEXTAFTER(arr, "/"))
)
...which is basically the same as what you've done, but with a double-negative to convert the text to numbers, rather that the VALUE function. Cheers! 🙂
I think we are just about in complete agreement. There are a couple of other techniques relevant to the array of arrays limitation. One is to replace the sequence of unit steps by a sequence of bisections as described by OwenPrice in his newsletter.
(5) Excel LAMBDA Spotlight: Bisected Map with BMAPλ | LinkedIn
Another technique that also appears to be far more efficient than REDUCE/VSTACK is to use SCAN or MAP but to convert the resulting array LET variables to Thunks. This was described by @lori_m in a