Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
String Fractions Convert to Numbers VBA
I have items like 4 7/8 X 7 in a cell. I am trying to put together a
function to multiply the two numbers together. I can do this with normal functions in Excl but I would like a function to do this job. So I would like this to return 34.125. Here is the code I have. Any help is appreciated, Jay Private Function FCArea() As String Dim Fnd As String Dim L As String, R As String, x As Integer Fnd = Range("B:B").Find(What:="Final Size:").Offset(2, 0).Value x = InStr(1, Fnd, "X", 1) L = Left(Fnd, x - 1) R = Right(Fnd, Len(Fnd) - x) FCArea = L * R End Function |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
String Fractions Convert to Numbers VBA
On Jan 19, 1:04*pm, jlclyde wrote:
I have items like 4 7/8 X 7 in a cell. *I am trying to put together a function to multiply the two numbers together. *I can do this with normal functions in Excl but I would like a function to do this job. So I would like this to return 34.125. * Here is the code I have. Any help is appreciated, Jay Private Function FCArea() As String * * Dim Fnd As String * * Dim L As String, R As String, x As Integer * * Fnd = Range("B:B").Find(What:="Final Size:").Offset(2, 0).Value * * x = InStr(1, Fnd, "X", 1) * * L = Left(Fnd, x - 1) * * R = Right(Fnd, Len(Fnd) - x) * * FCArea = L * R End Function I found a way to convert each fraction to a decimal and then multiply them. Here is that code. Jay Function Frac2Num(ByVal X As String) As Double Dim P As Integer, N As Double, Num As Double, Den As Double X = Trim$(X) P = InStr(X, "/") If P = 0 Then N = Val(X) Else Den = Val(Mid$(X, P + 1)) If Den = 0 Then Error 11 ' Divide by zero X = Trim$(Left$(X, P - 1)) P = InStr(X, " ") If P = 0 Then Num = Val(X) Else Num = Val(Mid$(X, P + 1)) N = Val(Left$(X, P - 1)) End If End If If Den < 0 Then N = N + Num / Den End If Frac2Num = N End Function |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
String Fractions Convert to Numbers VBA
An interesting question.
Function FCArea(r As Range) As Variant ' gsnuxx s = Split(r.Value, " X ") FCArea = Evaluate(s(0)) * Evaluate(s(1)) End Function so if D1 contained the string: 7 7/8 X 7 =FCArea(D1) would display 55.125 -- Gary''s Student - gsnu2007k "jlclyde" wrote: I have items like 4 7/8 X 7 in a cell. I am trying to put together a function to multiply the two numbers together. I can do this with normal functions in Excl but I would like a function to do this job. So I would like this to return 34.125. Here is the code I have. Any help is appreciated, Jay Private Function FCArea() As String Dim Fnd As String Dim L As String, R As String, x As Integer Fnd = Range("B:B").Find(What:="Final Size:").Offset(2, 0).Value x = InStr(1, Fnd, "X", 1) L = Left(Fnd, x - 1) R = Right(Fnd, Len(Fnd) - x) FCArea = L * R End Function |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
String Fractions Convert to Numbers VBA
Hi,
maybe Function EvalFrac(r As Range) EvalFrac = Application.Evaluate(Replace(LCase$(r.Value), "x", "*")) End Function call with =EvalFrac(a1) Mike "jlclyde" wrote: I have items like 4 7/8 X 7 in a cell. I am trying to put together a function to multiply the two numbers together. I can do this with normal functions in Excl but I would like a function to do this job. So I would like this to return 34.125. Here is the code I have. Any help is appreciated, Jay Private Function FCArea() As String Dim Fnd As String Dim L As String, R As String, x As Integer Fnd = Range("B:B").Find(What:="Final Size:").Offset(2, 0).Value x = InStr(1, Fnd, "X", 1) L = Left(Fnd, x - 1) R = Right(Fnd, Len(Fnd) - x) FCArea = L * R End Function |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
String Fractions Convert to Numbers VBA
Function conv(c) conv = Evaluate(Replace(c, "X", "*")) End Function JB http://boisgontierjacques.free.fr On 19 jan, 20:04, jlclyde wrote: I have items like 4 7/8 X 7 in a cell. *I am trying to put together a function to multiply the two numbers together. *I can do this with normal functions in Excl but I would like a function to do this job. So I would like this to return 34.125. * Here is the code I have. Any help is appreciated, Jay Private Function FCArea() As String * * Dim Fnd As String * * Dim L As String, R As String, x As Integer * * Fnd = Range("B:B").Find(What:="Final Size:").Offset(2, 0).Value * * x = InStr(1, Fnd, "X", 1) * * L = Left(Fnd, x - 1) * * R = Right(Fnd, Len(Fnd) - x) * * FCArea = L * R End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers to text string | Excel Discussion (Misc queries) | |||
Convert decimal to fractions | Excel Worksheet Functions | |||
Auto convert an alphanumeric string (CIS9638S) to numbers only? | Excel Worksheet Functions | |||
Auto convert an alphanumeric string (CIS9638S) to numbers only? | Excel Worksheet Functions | |||
Can i convert numbers into string format? | Excel Discussion (Misc queries) |