Forum Discussion

ESAM_HASHIM's avatar
ESAM_HASHIM
Copper Contributor
Apr 20, 2022
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 a...
developer
excel
Formulas and Functions
office 365
training
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Apr 20, 2022

    ESAM_HASHIM 

    Try this.

    =TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(TEXT(B8,"#  ????/????"),"/","#"),"#","</z><z>")&"</z></y>","//z"))
ESAM_HASHIM's avatar
ESAM_HASHIM
Copper Contributor
Apr 20, 2022

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

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Apr 20, 2022

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.

 

 

  • ESAM_HASHIM's avatar
    ESAM_HASHIM
    Copper Contributor
    Apr 20, 2022
    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.
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Apr 20, 2022

      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.

       

      • ESAM_HASHIM's avatar
        ESAM_HASHIM
        Copper Contributor
        Apr 20, 2022

        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

Resources