Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




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




Answer: Extract numerator from fraction
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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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, <altF11 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
Posted to microsoft.public.excel.worksheet.functions




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.34937E09 while my function NRN(1.362162855,9999) would return 10505/7712 with an absolute error of just 8.07054E09. MOD(10505,7712) would give you the numerator. Regards, Bernd 
#11
Posted to microsoft.public.excel.worksheet.functions




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 
Display Modes  


Similar Threads  
Thread  Forum  
Showing numerator in graph  Excel Discussion (Misc queries)  
How do I change fraction to fraction characters?  Excel Worksheet Functions  
fraction  Excel Discussion (Misc queries)  
Extract Unique Values, Then Extract Again to Remove Suffixes  Excel Discussion (Misc queries)  
How do I create a spread sheet w/a numerator/denomenator?  Excel Discussion (Misc queries) 