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