Extracting Numerator and Denominator to Difference Cells

[Taylorxu]

Hello,

I am attempting to extract both the numerator and denominator of a fraction into separate individual cells.
For example, a fraction of 209/69, I am seeking your assistance in a formula that can have the 209 and 69 in two separate cells. The fraction varies from one digit in the numerator to three digits in the numerator.

I have been using =LEFT(TEXT(MOD(A1,1),"#/##"),FIND("/",TEXT(MOD(a,1),"#/##"))-1) for the numerator and it is not working constantly with differentiating numerators.

Any assistance would be appreciated.

Thank you

 

[JoeMo]

If you want numbers instead of text, add +0 after the last ")" in both formulas.

Sheet1 (2)

	A	B	C
1	209/69	209	69

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(LEFT(SUBSTITUTE($A1,"/",REPT(" ",100)),100)) C1 =TRIM(RIGHT(SUBSTITUTE($A1,"/",REPT(" ",100)),100)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Taylorxu]

Thank you for your reply, when I enter =TRIM(LEFT(SUBSTITUTE($A1,"/",REPT(" ",100)),100))+0 I just receive the fraction as a whole, identical to that in A1

 

[JoeMo]

Thank you for your reply, when I enter =TRIM(LEFT(SUBSTITUTE($A1,"/",REPT(" ",100)),100))+0 I just receive the fraction as a whole, identical to that in A1

Format the cell as a number with 0 decimal places.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe...
Numerator
=LEFT(TEXT(A1,"###/###"),SEARCH("/",TEXT(A1,"###/###"))-1)

Denominator
=MID(TEXT(A1,"###/###"),SEARCH("/",TEXT(A1,"###/###"))+1,3)

Hope this helps

M.

 

[Taylorxu]

When I format it with as a number with zero decimal places the result is 3 in both formulas.

 

[JoeMo]

When I format it with as a number with zero decimal places the result is 3 in both formulas.

Very strange. Can you try entering 209/69 in A1 on a fresh sheet and the two formulas using the +0 in B1 and C1 respectively?

 

[mikerickson]

If A1 is a number, formatted to show as a fraction, something like

=TRIM(LEFT(SUBSTITUTE(TEXT($A1, "?/???"),"/",REPT(" ",100)),100))

 

[Taylorxu]

Thank you for your help.

The 209/69 fraction is a product of another calculation. When I use =TRIM(LEFT(SUBSTITUTE($A1,"/",REPT(" ",100)),100))+0 for 209/69 when the cell is a resulting formula and there are no decimal places your formula products 3.

When I enter in 209/69 manually and use =TRIM(LEFT(SUBSTITUTE($A1,"/",REPT(" ",100)),100)) I receive 209. How could this be?

 

[Marcelo Branco]

@Taylorxu
I assumed the cell is formatted as ###/### (custom format), so the cell contains a number, not a text.
Am i right? If so, try the formula in post 5

M.