Forum Discussion
Solved
How to split cell containing FRACTION into two cells including Numerator and denominator
Hi everyone here
If I have 4 cells containing the following fractions as example, my question is about the formula that can be applied for each for these cells to get two cells including Numerator and denominator for each fraction?
1/80
2/195
7/3744
7/8
Kind regards
Try this.
=TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(TEXT(B8,"# ????/????"),"/","#"),"#","</z><z>")&"</z></y>","//z"))
21 Replies
Looking back, I wonder why I selected CHOOSE to combine numerators and denominators into an array.
= LET( fraction, TEXT(values,"????/????"), numerator, TEXTBEFORE(fraction,"/"), denominator, TEXTAFTER(fraction,"/"), VALUE(HSTACK(numerator,denominator)) )Of course, had it not been for Microsoft's dreadful decision not to support nested arrays / arrays of arrays, life would be a lot simpler. Unfortunately
= LET( fraction, TEXT(values,"????/????"), TEXTSPLIT(fraction,"/") )returns only the numerators. A trick to return both numerator and denominators is to join the value array before splitting it two ways
= LET( fraction, TEXT(values,"????/????"), string, TEXTJOIN("\",,fraction), VALUE(TEXTSPLIT(string,"/","\")) )The sad fact is that the majority of formulas I now write contain obscure workarounds for the 'array of arrays' problem.
This is unfortunate because I would define a modern spreadsheet as an environment in which arrays of arrays may be manipulated to facilitate calculation. So why not do it properly? Excel with dynamic arrays is so nearly there in terms of functionality. The user interface needs further development to support testing and audit but that can evolve over time.
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
community discussion.I believe that the concept of relative referencing should be left behind in its entirety (as simply representing an inferior approach to scanning the terms of a list or an array). That is a difficult case to argue when results that are comprised of nested arrays (practically all spreadsheet solutions including data presented as pivot tables) are so difficult to generate.
I like Riny_van_Eekelen 's solution best. It can be spilled with a slight modification.
=LET(e,E8,CHOOSE({1,2},LEFT(e,FIND("/",e)-1),RIGHT(e,LEN(e)-FIND("/",e))))
Using the latest version of 365, two separate columns might be
= TEXTBEFORE(TEXT(values,"????/????"),"/") = TEXTAFTER(TEXT(values,"????/????"),"/")or to combine the two columns into a single 2D array
= LET( fraction, TEXT(values,"????/????"), numerator, TEXTBEFORE(fraction,"/"), denominator, TEXTAFTER(fraction,"/"), CHOOSE({1,2},numerator,denominator) )The original values were numbers rather than text and the number format uses improper fractions.
- Thank you Peter!!!
PeterBartholomew1
Thank you for your trial to help me but I would like to inform you that the formula provided by Detlef_Lewin is the most practical for my situation
I try to apply your formulas in My_Worksheet file but without any positive results, that's may be due to I am not expert in using those formulas you used in your example (fractions.xlsx)
Kind RegardsDon't feel bad about it. You have to be a 365 user, signed up to the beta channel, for those particular formulas to work. I post them because exploring new functionality interests me and in order to seed the idea that tomorrow's spreadsheet solutions may be very different from yesterday's.
ESAM_HASHIM Please see attached.
Thank you very much Riny_van_Eekelen
I can conclude from your file the following critical points to apply the two formula correctly as shown by replied file that attached here:
1, The first step is to change Format Cells in all empty cells to be Text format
2. Then I must inter all required fractions MANUALLY because (as concluded) Excel cannot accept all options in copy pest at all
From all above points please, I need to verify my idea
Warm Regards
ESAM_HASHIM But you can copy and paste your list of fractions. First format the column as Text, and then paste special as text. That's how I did in in my example.
