Separating Numerator and Denominator of a Fraction formatted Cell into two Separate Cells

[FRED_SHEET]

I have a cell (A1) with a decimal value of 0.34375 in an adjacent cell (B1) which is equal to (A1) but is formatted as a fraction function with two digits to give a fraction of 11/32. Is there any way I can convert the fraction into two different cells (C1) and (D1) one for the numerator (C1) and the other for the denominator (D1)? In other words I want C1 to display 11 and D1 to display 32.

 

[Rick Rothstein]

Try this for the numerator...

=0+LEFT(TEXT(A1,"00/00"),2)

and this one for the denominator...

=0+RIGHT(TEXT(A2,"00/00"),2)

Note that these formula are independent of the formula you have in Column B (if you remove that formula, the above formulas will still work).

 

[FRED_SHEET]

I tried this and it worked OK for the numerator but I had to change the formula for the denominator - I replaced A2 with A1 and it worked OK.

Now I just have to study the Excel functions to understand what is happening.

I tried it on several other fraction decimal values in column A and it worked OK. Thanks for your help!

 

[FRED_SHEET]

Try this for the numerator...

=0+LEFT(TEXT(A1,"00/00"),2)

and this one for the denominator...

=0+RIGHT(TEXT(A2,"00/00"),2)

Note that these formula are independent of the formula you have in Column B (if you remove that formula, the above formulas will still work).

Rick thanks for your response.

I am not sure what the 0 at the start of the two expressions above does. Could you please explain it to me?

Thank you.

 

[Rick Rothstein]

I am not sure what the 0 at the start of the two expressions above does. Could you please explain it to me?

The RIGHT and LEFT function return a text string (even if it looks like a number, it is not a number, it is text)... when you involve a text string that looks like a number in a mathematical calculation, Excel converts the text number to a real number so that it can complete the calculation... adding 0 to any number does not change the number so adding 0 to a text number simply converts it to its real number equivalent... that is what the 0+ at the start of each of those formulas is doing.

 

[FRED_SHEET]

The RIGHT and LEFT function return a text string (even if it looks like a number, it is not a number, it is text)... when you involve a text string that looks like a number in a mathematical calculation, Excel converts the text number to a real number so that it can complete the calculation... adding 0 to any number does not change the number so adding 0 to a text number simply converts it to its real number equivalent... that is what the 0+ at the start of each of those formulas is doing.

Thank you. I tried to find that on line but wasn't successful. I really appreciated forums like this to get the answers I need.

 

[Miles91]

I was actually trying to do this as well! However, some of the numbers in my list are decimal numbers (ex: 3.5/8.1). When I use the formula proposed, I only get the first and last digits. (in the example, would give me 3 and 0.1 separately). Anything to do about this? Thanks alot!

 

[Rick Rothstein]

Do the numerator and denominator always have a single digit before the decimal point and single digit after it?

 

[Miles91]

No. It's either single digit or double digit.

 

[Miles91]

oh sorry, after it is always only one decimal!