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

FRED_SHEET

New Member
Joined
Oct 13, 2017
Messages
37
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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

New Member
Joined
Oct 13, 2017
Messages
37
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

New Member
Joined
Oct 13, 2017
Messages
37
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? :confused:

Thank you.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
I am not sure what the 0 at the start of the two expressions above does. Could you please explain it to me? :confused:
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

New Member
Joined
Oct 13, 2017
Messages
37
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. :)
 

Forum statistics

Threads
1,081,693
Messages
5,360,664
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top