Extract numerator from fraction

[tf]

I have a column of decimal values in an Excel 2003. I have converted the
values to fractions using the "format cell" function and would like to
extract the numerator values from the fractions. I thought I could do this
by converting text to columns with the "/" as a delimiter, such that the
numerator and denominator values would be returned in separate columns. The
problem is that even though my values show as a fraction, they are still read
as decimals by Excel and the text to columns won't work. I've tried using
the "paste special" function to replace the decimals with the fraction
values, but that did not work either.

Any ideas on how to get Excel to read the values as fractions so that I can
use the text to columns function; or another method that will allow me to
extract the numerator values? (I don't have a common denominator, so I can't
use the dollar functions)

Thanks!

 

[Ron Coderre]

Try something like this:

A1: (a number....displayed as a fraction or not)

This formula returns the 32nd's numerator, if any :
=LEFT(TEXT(MOD(A1,1),"#/32"),FIND("/",TEXT(MOD(A1,1),"#/32"))-1)

Example:
A1: =5/32
B1 returns 5

and...this formula returns whatever numerator Excel uses with a general
fraction display:
=LEFT(TEXT(MOD(A1,1),"#/##"),FIND("/",TEXT(MOD(A1,1),"#/##"))-1)

Example:
A1: =1 3/13.....converts to: =1.23076923076923
B1 returns 3

Note though, without knowing the denominator...does only knowing the
numerator help?

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Niek Otten]

< I thought I could do this by converting text to columns with the "/" as a delimiter>

You were on the right track.

This is easiest with helper columns, but can be done in one formula:

=RIGHT(TEXT(A1,"? ?/?"),LEN(TEXT(A1,"? ?/?"))-FIND("/",TEXT(A1,"? ?/?")))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a column of decimal values in an Excel 2003. I have converted the
| values to fractions using the "format cell" function and would like to
| extract the numerator values from the fractions. I thought I could do this
| by converting text to columns with the "/" as a delimiter, such that the
| numerator and denominator values would be returned in separate columns. The
| problem is that even though my values show as a fraction, they are still read
| as decimals by Excel and the text to columns won't work. I've tried using
| the "paste special" function to replace the decimals with the fraction
| values, but that did not work either.
|
| Any ideas on how to get Excel to read the values as fractions so that I can
| use the text to columns function; or another method that will allow me to
| extract the numerator values? (I don't have a common denominator, so I can't
| use the dollar functions)
|
| Thanks!

 

[Stephen]

I have a column of decimal values in an Excel 2003. I have converted the
values to fractions using the "format cell" function and would like to
extract the numerator values from the fractions. I thought I could do
this
by converting text to columns with the "/" as a delimiter, such that the
numerator and denominator values would be returned in separate columns.
The
problem is that even though my values show as a fraction, they are still
read
as decimals by Excel and the text to columns won't work. I've tried using
the "paste special" function to replace the decimals with the fraction
values, but that did not work either.

Any ideas on how to get Excel to read the values as fractions so that I
can
use the text to columns function; or another method that will allow me to
extract the numerator values? (I don't have a common denominator, so I
can't
use the dollar functions)

Thanks!

Why are you trying to do this? What will you do with the numerator? What do
you want to achieve? I ask because formatting a cell as "fraction" (of some
specified kind) just displays an approximation to the value in the cell that
will fit with the specified format. The error is variable. The "fraction"
format is intended just for display, not for subsequent calculation. It's
difficult to see what use the numerator of such an approximate fraction
could possibly be, which leads me to question whether or not you understand
the "fraction" format and its (severe) limitations.

 

[Niek Otten]

Hi Stephen,

I don't think you're right, but you made me think and test, so I changed my formula to

=RIGHT(TEXT(A1,"? ?/????????"),LEN(TEXT(A1,"? ?/????????"))-FIND("/",TEXT(A1,"? ?/????????")))

Thanks!
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Stephen" <none> wrote in message | | >I have a column of decimal values in an Excel 2003. I have converted the
| > values to fractions using the "format cell" function and would like to
| > extract the numerator values from the fractions. I thought I could do
| > this
| > by converting text to columns with the "/" as a delimiter, such that the
| > numerator and denominator values would be returned in separate columns.
| > The
| > problem is that even though my values show as a fraction, they are still
| > read
| > as decimals by Excel and the text to columns won't work. I've tried using
| > the "paste special" function to replace the decimals with the fraction
| > values, but that did not work either.
| >
| > Any ideas on how to get Excel to read the values as fractions so that I
| > can
| > use the text to columns function; or another method that will allow me to
| > extract the numerator values? (I don't have a common denominator, so I
| > can't
| > use the dollar functions)
| >
| > Thanks!
|
| Why are you trying to do this? What will you do with the numerator? What do
| you want to achieve? I ask because formatting a cell as "fraction" (of some
| specified kind) just displays an approximation to the value in the cell that
| will fit with the specified format. The error is variable. The "fraction"
| format is intended just for display, not for subsequent calculation. It's
| difficult to see what use the numerator of such an approximate fraction
| could possibly be, which leads me to question whether or not you understand
| the "fraction" format and its (severe) limitations.
|
|

 

[Bernd P]

Hello Niek,

I agree with you.

My suggestion is to use Oliver Aberth's algorithm, for example:
http://www.sulprobil.com/html/nearest_rational_number.html

Regards,
Bernd

 

[tf]

Thanks all for your input.

I've been working with Niek's amended suggestion (though I had to replace
"right" with "left" to get the numerator instead of denominator). This
solution has worked for most of my values, but I have a few that are giving
me trouble.

Most of my fractions are four digit numerators with four digit denominators.
For those fractions with five digit numerators, the numerator that is
returned is missing the fifth digit. I've tried altering the number of
question marks in the formula, but have not had any luck.

Any further suggestions?

Here's an example:
The decimal "1.362162855" is formatted as the fraction "10757/7897" (using
custom formatting "####/####"; I've also tried altering this formatting to
get the correct numerator return). Using Niek's formula, the numerator
returned is "1075".

p.s. In response to Stephen, I actually need both numerator and denominator,
but once I figure out how to get one I can easily get the other, so for
simplicity didn't mention this. I do realize that the fraction is not the
exact representation of the decimal, but I think it is close enough for my
purposes, which are a little to complicated to go into!

 

[Ron Rosenfeld]

I have a column of decimal values in an Excel 2003. I have converted the
values to fractions using the "format cell" function and would like to
extract the numerator values from the fractions. I thought I could do this
by converting text to columns with the "/" as a delimiter, such that the
numerator and denominator values would be returned in separate columns. The
problem is that even though my values show as a fraction, they are still read
as decimals by Excel and the text to columns won't work. I've tried using
the "paste special" function to replace the decimals with the fraction
values, but that did not work either.

Any ideas on how to get Excel to read the values as fractions so that I can
use the text to columns function; or another method that will allow me to
extract the numerator values? (I don't have a common denominator, so I can't
use the dollar functions)

Thanks!

Relatively easy with VBA, if I understand what you want:

Here is a UDF that will return either the numerator or the denominator.

I'm not sure if you wanted this, but it uses a format based on having the same
denominator as in the cell format, but there is no separate digit.

In other words, something like 1 3/8 would return 11 for denominator and 8 for
numerator. If you would rather have it return a "3", then we can easily make
that change.

In any event, to enter the UDF, <alt-F11> opens the VBEditor. Ensure your
project is highlighted in the Project Explorer Window, then Insert/Module and
paste the code below into the window that opens.

To use the UDF, enter a formula of the type:

=FxPart(cell_ref,[Part]

into some cell. Part is optional. If omitted, or "N", the formula will return
the numerator. If other than "N", it will return the denominator.

===========================================
Option Explicit
Function FxPart(c As Range, Optional Part As String = "N") As Double
Dim re As Object, mc As Object
Dim s As String
Dim f As String

f = c.NumberFormat
f = "#" & Mid(f, InStr(1, f, "/"), 255)
s = Application.WorksheetFunction.Text(c.Value, f)
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\d+)/(\d+)"
If re.test(s) = True Then
Set mc = re.Execute(s)
If Part = "N" Then
FxPart = mc(0).submatches(0)
Else
FxPart = mc(0).submatches(1)
End If
End If
End Function
====================================
--ron

 

[Bernd P]

Hello,

I still suggest to take my NRN function.

The solution 10757/7897 for 1.362162855 has an absolute error of
8.34937E-09 while my function NRN(1.362162855,9999) would return
10505/7712 with an absolute error of just 8.07054E-09.

MOD(10505,7712) would give you the numerator.

Regards,
Bernd

 

[Lori]

Try converting the fractions to text then you can do the Text to columns or
an Edit > Replace.

This can be achieved by formatting a column as text and then copying and
pasting the column either to and from notepad or with the office clipboard on
the edit menu (although if the data range is small you need to undo and then
paste special as text after clicking the paste icon)