Show denominator and numerator of fraction

Cosmos75 · Apr 16th, 2002, 11:11 AM

If I have a cell to be formatted as a fraction, is there anyway to get the denominator and numerator of the fraction.

E.g.

If Excel has .26163 as 45/172 in Cell A1, is there anyway to get a cell B1 to show 45 and Cell C1 to show 172?

FYI, 3.26163 lightyears is 1 Parsec.

IML · Apr 16th, 2002, 11:18 AM

There may be a better way, but
num
=LEFT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0

denom
=RIGHT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0

Yogi Anand · Apr 16th, 2002, 11:21 AM

Quote:

On 2002-04-16 10:11, Cosmos75 wrote:
If I have a cell to be formatted as a fraction, is there anyway to get the denominator and numerator of the fraction.

E.g.

If Excel has .26163 as 45/172 in Cell A1, is there anyway to get a cell B1 to show 45 and Cell C1 to show 172?

FYI, 3.26163 lightyears is 1 Parsec.

I know there are probably more elegant solutions, for this one use

in cell B1 =LEFT(A1,FIND("/",A1,1)-1)
in cell C1 =RIGHT(A1,FIND("/",A1,1))

HTH

Cosmos75 · Apr 16th, 2002, 12:00 PM

IML,

Thanks! Works Great!

Yogi,

Your formula gave me a #VALUE error message. But Thank You for trying!!

[ This Message was edited by: Cosmos75 on 2002-04-16 11:01 ]

Yogi Anand · Apr 16th, 2002, 12:13 PM

Hi cosmos75:
I did check my formula by entering
45/172 in cell A1, then

=LEFT(A1,FIND("/",A1,1)-1)+0 in cell B1 gives 45, and

=RIGHT(A1,FIND("/",A1,1))+0 in cell C1 gives 172

Please enter just the fraction 45/172 and not =45/172 in cell A1

I would like to see what error you got ... it does work for me!

IML · Apr 16th, 2002, 12:21 PM

Quote:

On 2002-04-16 11:13, Yogi Anand wrote:
Hi cosmos75:
I did check my formula by entering
45/172 in cell A1, then

=LEFT(A1,FIND("/",A1,1)-1)+0 in cell B1 gives 45, and

=RIGHT(A1,FIND("/",A1,1))+0 in cell C1 gives 172

Please enter just the fraction 45/172 and not =45/172 in cell A1

I would like to see what error you got ... it does work for me!

Yogi,

.26163 is entered in a cell formatted as a fraction. Therefore it errors out when it can't find a "/" in .26163

Yogi Anand · Apr 16th, 2002, 12:27 PM

Hi cosmos75 and IML:
IML's formula is more comprehensive and works when 45/172 is entered as a text string or as a number (as in =45/172). The one that I proposed works only when 45/172 is entered as a text string.

Regards!

Yogi Anand · Apr 16th, 2002, 02:59 PM

Quote:

Yogi,

.26163 is entered in a cell formatted as a fraction. Therefore it errors out when it can't find a "/" in .26163

Hi IML:
Yes Sir ... Agreed ... as I stated before you formula is more comprehensive and is the one I would use. T H A N K S !

Chris Davison · Apr 16th, 2002, 04:07 PM

Quote:

On 2002-04-16 10:11, Cosmos75 wrote:
FYI, 3.26163 lightyears is 1 Parsec.

Hey Cosmos,

You sound as if you're in Astrophysics.... have you read Cliff Stohl's book The Cuckoo's Egg ? It's a good old hoot, mixing computers against a backdrop of astrophysics !

Cosmos75 · Apr 17th, 2002, 10:18 AM

Chris Davidson,

As an undergrad I double majored in Astrophysics and Psychology (LONG STORY!!). I haven't completed my degree due to money problems. Probably just go back and finish up Psychology degree and then on to a MA/PhD Cognitive Psychology program.

Nope, I haven't read that yet. Will look into that! Thanks for the recommendation!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 11:11 AM	#1
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	If I have a cell to be formatted as a fraction, is there anyway to get the denominator and numerator of the fraction. E.g. If Excel has .26163 as 45/172 in Cell A1, is there anyway to get a cell B1 to show 45 and Cell C1 to show 172? FYI, 3.26163 lightyears is 1 Parsec.

Apr 16th, 2002, 11:18 AM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	There may be a better way, but num =LEFT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0 denom =RIGHT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0

Apr 16th, 2002, 12:00 PM	#4
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	IML, Thanks! Works Great! Yogi, Your formula gave me a #VALUE error message. But Thank You for trying!! [ This Message was edited by: Cosmos75 on 2002-04-16 11:01 ]

Apr 16th, 2002, 12:13 PM	#5
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi cosmos75: I did check my formula by entering 45/172 in cell A1, then =LEFT(A1,FIND("/",A1,1)-1)+0 in cell B1 gives 45, and =RIGHT(A1,FIND("/",A1,1))+0 in cell C1 gives 172 Please enter just the fraction 45/172 and not =45/172 in cell A1 I would like to see what error you got ... it does work for me!

Apr 16th, 2002, 12:27 PM	#7
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi cosmos75 and IML: IML's formula is more comprehensive and works when 45/172 is entered as a text string or as a number (as in =45/172). The one that I proposed works only when 45/172 is entered as a text string. Regards!

Apr 17th, 2002, 10:18 AM	#10
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	Chris Davidson, As an undergrad I double majored in Astrophysics and Psychology (LONG STORY!!). I haven't completed my degree due to money problems. Probably just go back and finish up Psychology degree and then on to a MA/PhD Cognitive Psychology program. Nope, I haven't read that yet. Will look into that! Thanks for the recommendation!