Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tf tf is offline
external usenet poster
 
Posts: 7
Default Extract numerator from fraction

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!
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Extract numerator from fraction

  1. Select the column of fractions that you want to extract the numerator values from.
  2. Click on the "Insert Function" button (fx) in the formula bar.
  3. In the "Insert Function" dialog box, type "FIND" in the search box and click "Go".
  4. Select "FIND" from the list of functions and click "OK".
  5. In the "Find" dialog box, type "/" in the "Find what" box and click "OK". This will return the position of the "/" character in each cell.
  6. Click on the "Insert Function" button (fx) again and select "LEFT" from the list of functions.
  7. In the "LEFT" dialog box, click on the cell that contains the fraction and enter the number returned by the FIND function in the "num_chars" box. This will return the numerator value.
  8. Click "OK" to close the dialog box.
  9. Drag the formula down to apply it to the rest of the cells in the column.

This should give you a column of numerator values extracted from the original column of fractions.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Extract numerator from fraction

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)

"tf" 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!



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default Extract numerator from fraction

< 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

"tf" 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!


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 364
Default Extract numerator from fraction

"tf" 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.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default Extract numerator from fraction

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 ...
| "tf" 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.
|
|


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Extract numerator from fraction

Hello Niek,

I agree with you.

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

Regards,
Bernd
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tf tf is offline
external usenet poster
 
Posts: 7
Default Extract numerator from fraction

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!

"Bernd P" wrote:

Hello Niek,

I agree with you.

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

Regards,
Bernd

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Extract numerator from fraction

On Wed, 12 Dec 2007 07:56:01 -0800, tf wrote:

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
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Extract numerator from fraction

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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 272
Default Extract numerator from fraction

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)

"tf" wrote:

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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Showing numerator in graph JR-BHS Excel Discussion (Misc queries) 1 April 12th 07 05:16 PM
How do I change fraction to fraction characters? cmcqueary2806 Excel Worksheet Functions 1 November 2nd 06 10:58 PM
fraction b166er Excel Discussion (Misc queries) 1 August 3rd 06 06:12 PM
Extract Unique Values, Then Extract Again to Remove Suffixes Karl Burrows Excel Discussion (Misc queries) 23 June 25th 05 10:37 PM
How do I create a spread sheet w/a numerator/denomenator? Denise Excel Discussion (Misc queries) 4 May 18th 05 06:24 PM


All times are GMT +1. The time now is 06:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"