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

Extract Fractional Numbers as Numbers NOT Date
 

How did you do the extraction

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

"MrBill" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

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" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

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" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

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" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

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" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

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" wrote in message
...
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" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

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" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

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" wrote in message
...
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" wrote in message
...
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

Extract Fractional Numbers as Numbers NOT Date
 

On Wed, 2 Nov 2005 14:03:05 -0800, "CADManBill"
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

Extract Fractional Numbers as Numbers NOT Date
 

"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.
....
"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")),2 56))

if the leading number of nondigits is variable.

Extract Fractional Numbers as Numbers NOT Date
 

On Wed, 2 Nov 2005 14:03:05 -0800, "CADManBill"
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