Extract Fractional Numbers as Numbers NOT Date

[MrBill]

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

 

[Bernard Liengme]

How did you do the extraction

 

[Bernard Liengme]

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.

 

[MrBill]

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?

 

[Gary''s Student]

A2 is just fine. Enter the following UDF:

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

and it will give you the .375 you desire.

 

[MrBill]

How do I enter/Use the "UDF"?

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

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?

 

[Peo Sjoblom]

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)

 

[Gary''s Student]

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

How do I enter/Use the "UDF"?

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

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?


:

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

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

 

[CADManBill]

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.

 

[Ron Rosenfeld]

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

 

[Harlan Grove]

A2 is just fine. Enter the following UDF:

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

and it will give you the .375 you desire. ....

....

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.

 

[Ron Rosenfeld]

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