How to split cell containing FRACTION into two cells including Numerator and denominator

Occasional Contributor

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?



Kind regards


14 Replies

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.



Please can you tell me how to PASTE SPECIAL as text to make the formula possible, because I didn't find any way rather than inter all required fractions MANUALLY to apply the specified formulas, you can copy the example sheet to a new sheet to be more understand your idea
Much apricated.

@ESAM_HASHIM Not sure I understand your question. But in order to help you further, I'd need to have some more info. Where do the fractions come from? I understood that you already had them in a list in Excel and needed to split them. If not, pease clarify.

I just copied the fractions from this forum page into my own Excel and needed to use paste special into a text column, but perhaps you don't have to do that at all.


Thank you  @Riny_van_Eekelen so much for your generosity

Please let me to explain the steps that applied  by going to My_Worksheet file
1. Firstly, I pasted the Column A from another worksheets after changing the format cell to view fractions
2. I got the values shown in B column by 2 equations shown in the file
3. I have repeated values shown in column E MANUALLY from B column to be able to apply the two formulas in columns F & G

My question was are there another way rather than repeat values manually in column E to be able to split all cells as shown in columns F & G

best response confirmed by ESAM_HASHIM (Occasional Contributor)


Try this.

=TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(TEXT(B8,"#  ????/????"),"/","#"),"#","</z><z>")&"</z></y>","//z"))

Thank you so much @Detlef Lewin
I highly appreciate the info. This will help me enormously.


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,"/"),

The original values were numbers rather than text and the number format uses improper fractions.


@Peter Bartholomew
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 Regards


I like @Riny_van_Eekelen 's solution best. It can be spilled with a slight modification.



Don'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.

@Peter Bartholomew 

I feel that there are the more covering info about my request to be as a best reference
you are most welcome, don't worry about my feeling
Have a nice day!