VBA to convert fractions

[mark9097]

Hi,

Can someone help me, what i need is some vba code to convert a price with a fraction in it to a decimal figure.

Eg in cell A1 you have 1.03 1/2
B1 56.05 47/64
ect
i need to convert to 1.035

thanks
Mark

 

[Richard Schollar]

Hi Mark

There may be easier ways, but the following will work:

Sub ConvFractions()
Dim r As Range, strText As String
For Each r In Selection
    strText = Replace(Application.WorksheetFunction.Trim(r.Text), " ", "+")
    r.Value = Evaluate(strText)
Next
End Sub

Will convert all cells in the selected range at the point it is run.

 

[mark9097]

Thanks for that,

Just one more thing though, it works well but adds the fraction on to the price instead of being the next decimal placing

eg 1.03 1/5 becomes 1.53 not 1.035

but thing get a bit more complicated when we get to 102.05 47/64, the fraction shows as 0.74 but adding it on to the price will geve me 102.050.74

Hope this makes sense

 

[mark9097]

Sub ConvFractions()
Dim r As Range, strText As String
For Each r In Selection
strText = Replace(Application.WorksheetFunction.Trim(r.Text), " ", "+")
r.Value = Evaluate(strText)
Next
End Sub

If i replace the + with a & then i tags the fraction amount on the end, but then on some prices i get a figure like 103.35.005 which should be 103.35005.

can anyone tell me how to get the second "." out of the number.

Thanks

 

[Richard Schollar]

Hi

Sorry, failed to appreciate exactly what it was you were after - give this one a shot:

Sub ConvFractions()
Dim r As Range, aVal As Variant, strText As String
For Each r In Selection
    aVal = Split(Application.WorksheetFunction.Trim(r.Text))
    strText = aVal(0) & Replace(Format(Evaluate(aVal(1)), "0.000000"), "0.", "")
    r.Value = Evaluate(strText)
Next
End Sub

 

[Norie]

Perhaps a UDF?

Function ConvFrac(rng As Range)
Dim arrVals
    arrVals = Split(rng.Value, " ")
    
    ConvFrac = arrVals(0) & Mid(Evaluate(arrVals(1)), 3)
End Function