Extract Fractional Numbers as Numbers NOT Date

I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
Calculation it uses the serial no. of the date rather than a number.

Cell A1 PL3/8x5
Cell A2 3/8 extracted from A1
Cell A3 =A28*1 Displays 38419.00
I need it to return .375

Is there a format or calc to fix this

How did you do the extraction

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MrBill" <[email protected]> wrote in message
news:[email protected]...
>I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> Calculation it uses the serial no. of the date rather than a number.
>
> Cell A1 PL3/8x5
> Cell A2 3/8 extracted from A1
> Cell A3 =A28*1 Displays 38419.00
> I need it to return .375
>
> Is there a format or calc to fix this

How did you do the extraction. Try changing it to generate =3/8 (i.e add the
equal sign) or generate 0 3/8 which is how a fraction is entered.

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MrBill" <[email protected]> wrote in message
news:[email protected]...
>I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> Calculation it uses the serial no. of the date rather than a number.
>
> Cell A1 PL3/8x5
> Cell A2 3/8 extracted from A1
> Cell A3 =A28*1 Displays 38419.00
> I need it to return .375
>
> Is there a format or calc to fix this

I did the Extraction using
A2 =MID(A1,FIND("L",A1)+1,LEN(A1)-FIND("L",A1,1)-(LEN(A1)-FIND("x",A1)+1))
Is there a better way?


"Bernard Liengme" wrote:

> How did you do the extraction. Try changing it to generate =3/8 (i.e add the
> equal sign) or generate 0 3/8 which is how a fraction is entered.
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "MrBill" <[email protected]> wrote in message
> news:[email protected]...
> >I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> > Calculation it uses the serial no. of the date rather than a number.
> >
> > Cell A1 PL3/8x5
> > Cell A2 3/8 extracted from A1
> > Cell A3 =A28*1 Displays 38419.00
> > I need it to return .375
> >
> > Is there a format or calc to fix this
>
>
>

A2 is just fine. Enter the following UDF:

Function calcit()
calcit = Evaluate("=" & [A2])
End Function

and it will give you the .375 you desire.
--
Gary's Student


"MrBill" wrote:

> I did the Extraction using
> A2 =MID(A1,FIND("L",A1)+1,LEN(A1)-FIND("L",A1,1)-(LEN(A1)-FIND("x",A1)+1))
> Is there a better way?
>
>
> "Bernard Liengme" wrote:
>
> > How did you do the extraction. Try changing it to generate =3/8 (i.e add the
> > equal sign) or generate 0 3/8 which is how a fraction is entered.
> >
> > --
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "MrBill" <[email protected]> wrote in message
> > news:[email protected]...
> > >I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> > > Calculation it uses the serial no. of the date rather than a number.
> > >
> > > Cell A1 PL3/8x5
> > > Cell A2 3/8 extracted from A1
> > > Cell A3 =A28*1 Displays 38419.00
> > > I need it to return .375
> > >
> > > Is there a format or calc to fix this
> >
> >
> >

How do I enter/Use the "UDF"?


"Gary''s Student" wrote:

> A2 is just fine. Enter the following UDF:
>
> Function calcit()
> calcit = Evaluate("=" & [A2])
> End Function
>
> and it will give you the .375 you desire.
> --
> Gary's Student
>
>
> "MrBill" wrote:
>
> > I did the Extraction using
> > A2 =MID(A1,FIND("L",A1)+1,LEN(A1)-FIND("L",A1,1)-(LEN(A1)-FIND("x",A1)+1))
> > Is there a better way?
> >
> >
> > "Bernard Liengme" wrote:
> >
> > > How did you do the extraction. Try changing it to generate =3/8 (i.e add the
> > > equal sign) or generate 0 3/8 which is how a fraction is entered.
> > >
> > > --
> > > Bernard V Liengme
> > > www.stfx.ca/people/bliengme
> > > remove caps from email
> > >
> > > "MrBill" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> > > > Calculation it uses the serial no. of the date rather than a number.
> > > >
> > > > Cell A1 PL3/8x5
> > > > Cell A2 3/8 extracted from A1
> > > > Cell A3 =A28*1 Displays 38419.00
> > > > I need it to return .375
> > > >
> > > > Is there a format or calc to fix this
> > >
> > >
> > >

One way

=LEFT(A2,FIND("/",A2)-1)/SUBSTITUTE(A2,LEFT(A2,FIND("/",A2)),"")

or in one formula

=MID(A1,FIND("L",A1)+1,FIND("/",A1)-FIND("L",A1)-1)/MID(A1,FIND("/",A1)+1,FI
ND("x",A1)-FIND("/",A1)-1)


--

Regards,

Peo Sjoblom


"MrBill" <[email protected]> wrote in message
news:[email protected]...
> I did the Extraction using
> A2
=MID(A1,FIND("L",A1)+1,LEN(A1)-FIND("L",A1,1)-(LEN(A1)-FIND("x",A1)+1))
> Is there a better way?
>
>
> "Bernard Liengme" wrote:
>
> > How did you do the extraction. Try changing it to generate =3/8 (i.e add
the
> > equal sign) or generate 0 3/8 which is how a fraction is entered.
> >
> > --
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "MrBill" <[email protected]> wrote in message
> > news:[email protected]...
> > >I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> > > Calculation it uses the serial no. of the date rather than a number.
> > >
> > > Cell A1 PL3/8x5
> > > Cell A2 3/8 extracted from A1
> > > Cell A3 =A28*1 Displays 38419.00
> > > I need it to return .375
> > >
> > > Is there a format or calc to fix this
> >
> >
> >

Its not hard at all. Check out:


http://www.mvps.org/dmcritchie/excel/getstarted.htm

many useful macros are very tiny and easy to paste into a module
--
Gary's Student


"MrBill" wrote:

> How do I enter/Use the "UDF"?
>
>
> "Gary''s Student" wrote:
>
> > A2 is just fine. Enter the following UDF:
> >
> > Function calcit()
> > calcit = Evaluate("=" & [A2])
> > End Function
> >
> > and it will give you the .375 you desire.
> > --
> > Gary's Student
> >
> >
> > "MrBill" wrote:
> >
> > > I did the Extraction using
> > > A2 =MID(A1,FIND("L",A1)+1,LEN(A1)-FIND("L",A1,1)-(LEN(A1)-FIND("x",A1)+1))
> > > Is there a better way?
> > >
> > >
> > > "Bernard Liengme" wrote:
> > >
> > > > How did you do the extraction. Try changing it to generate =3/8 (i.e add the
> > > > equal sign) or generate 0 3/8 which is how a fraction is entered.
> > > >
> > > > --
> > > > Bernard V Liengme
> > > > www.stfx.ca/people/bliengme
> > > > remove caps from email
> > > >
> > > > "MrBill" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> > > > > Calculation it uses the serial no. of the date rather than a number.
> > > > >
> > > > > Cell A1 PL3/8x5
> > > > > Cell A2 3/8 extracted from A1
> > > > > Cell A3 =A28*1 Displays 38419.00
> > > > > I need it to return .375
> > > > >
> > > > > Is there a format or calc to fix this
> > > >
> > > >
> > > >

I guess I should have been more detailed in my example.
Fact is that I have many strings similar to the example that need to be
extracted and calculated the same way. But the characters leading and
trailing the "/" differ.

More Defined Example of Strings
A1 PL3/8x5
B1 PL3/8x5 1/2
C1 PL1 1/2x3
D1 PL1 1/2x3 1/2
E1 PL13/16x15/16
On... and On...

The suggested formulas only consider One character on each side of the "/".
Basically, if the "PL" was replaced with"=" and the "x" replaced with "*" it
would look like what I an trying to accomplish.

Please excuse the name change, Earlier I was using my account at work and
now my account at home.

"Peo Sjoblom" wrote:

> One way
>
> =LEFT(A2,FIND("/",A2)-1)/SUBSTITUTE(A2,LEFT(A2,FIND("/",A2)),"")
>
> or in one formula
>
> =MID(A1,FIND("L",A1)+1,FIND("/",A1)-FIND("L",A1)-1)/MID(A1,FIND("/",A1)+1,FI
> ND("x",A1)-FIND("/",A1)-1)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "MrBill" <[email protected]> wrote in message
> news:[email protected]...
> > I did the Extraction using
> > A2
> =MID(A1,FIND("L",A1)+1,LEN(A1)-FIND("L",A1,1)-(LEN(A1)-FIND("x",A1)+1))
> > Is there a better way?
> >
> >
> > "Bernard Liengme" wrote:
> >
> > > How did you do the extraction. Try changing it to generate =3/8 (i.e add
> the
> > > equal sign) or generate 0 3/8 which is how a fraction is entered.
> > >
> > > --
> > > Bernard V Liengme
> > > www.stfx.ca/people/bliengme
> > > remove caps from email
> > >
> > > "MrBill" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I exttract 3/8 from an Alpha-Mumeric String. When I try to use it in
> > > > Calculation it uses the serial no. of the date rather than a number.
> > > >
> > > > Cell A1 PL3/8x5
> > > > Cell A2 3/8 extracted from A1
> > > > Cell A3 =A28*1 Displays 38419.00
> > > > I need it to return .375
> > > >
> > > > Is there a format or calc to fix this
> > >
> > >
> > >
>
>
>

On Wed, 2 Nov 2005 14:03:05 -0800, "CADManBill"
<[email protected]> wrote:

>I guess I should have been more detailed in my example.
>Fact is that I have many strings similar to the example that need to be
>extracted and calculated the same way. But the characters leading and
>trailing the "/" differ.
>
>More Defined Example of Strings
>A1 PL3/8x5
>B1 PL3/8x5 1/2
>C1 PL1 1/2x3
>D1 PL1 1/2x3 1/2
>E1 PL13/16x15/16
>On... and On...
>
>The suggested formulas only consider One character on each side of the "/".
>Basically, if the "PL" was replaced with"=" and the "x" replaced with "*" it
>would look like what I an trying to accomplish.
>
>Please excuse the name change, Earlier I was using my account at work and
>now my account at home.
>

That explanation makes constructing a UDF pretty simple.

<alt><F11> opens the VB Editor. 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 this formula, enter =EV(cell_ref) into some cell where cell_ref is a
cell you wish to evaluate as you describe (e.g. =EV(A1).

===================================
Option Explicit

Function Ev(str As String) As Double
Dim Fml As String

Fml = Replace(str, "PL", "")
Fml = Replace(Fml, "x", "*")

Ev = Evaluate(Fml)
End Function
============================


--ron

"Gary''s Student" <[email protected]> wrote...
>A2 is just fine. Enter the following UDF:
>
>Function calcit()
>calcit = Evaluate("=" & [A2])
>End Function
>
>and it will give you the .375 you desire.
....
>>"Bernard Liengme" wrote:
>>>How did you do the extraction. Try changing it to generate =3/8
>>>(i.e add the equal sign) or generate 0 3/8 which is how a
>>>fraction is entered.
....

A udf for this, ESPECIALLY a udf using hardcoded cell references, is a
REALLY STUPID suggestion. (If you're going to suggest a udf, at least use
sensible function arguments.)

Bernard was on the right track. All it takes is using the formula

=--("0 "&MID(A1,3,FIND("x",A1)-3))

if the leading text is always 2 characters, or the array formula

=--("0 "&MID(LEFT(A1,FIND("x",A1)-1),
MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),256))

if the leading number of nondigits is variable.

On Wed, 2 Nov 2005 14:03:05 -0800, "CADManBill"
<[email protected]> wrote:

>I guess I should have been more detailed in my example.
>Fact is that I have many strings similar to the example that need to be
>extracted and calculated the same way. But the characters leading and
>trailing the "/" differ.
>
>More Defined Example of Strings
>A1 PL3/8x5
>B1 PL3/8x5 1/2
>C1 PL1 1/2x3
>D1 PL1 1/2x3 1/2
>E1 PL13/16x15/16
>On... and On...
>
>The suggested formulas only consider One character on each side of the "/".
>Basically, if the "PL" was replaced with"=" and the "x" replaced with "*" it
>would look like what I an trying to accomplish.
>
>Please excuse the name change, Earlier I was using my account at work and
>now my account at home.
>

Another option would be to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr/

You could then use these "regular expression" formulas:

1st Fractional Number: =REGEX.MID(A1,"(\d+\s)?\d+(/\d+)?",1)
2nd Fractional Number: =REGEX.MID(A1,"(\d+\s)?\d+(/\d+)?",2)

Product of the two:

=EVAL(REGEX.MID(A1,"(\d+\s)?\d+(/\d+)?",1)&"*"&REGEX.MID(A1,"(\d+\s)?\d+(/\d+)?",2))



--ron