Need to convert a fraction to a decimal

[ouadad]

The output of a program provides a fraction like 6519/231 . I need to convert this to a decimal. I tried going from "general" to "number" in the drop down tab, but it does nothng. I really need to be able to put an equal sign in front of every entry, but I have hundreds. Any advice?

 

[DushiPunda]

How does the data get to excel? Is there any other data that you don't want to become a formula?

 

[DushiPunda]

You could have a macro to do it that you manually trigger, or you can have a macro to do it that's triggered by the Worksheet_Change event...

the basic premise is going to be:

Worksheets("Sheet1").Range("A1").Formula = "=" & Worksheets("Sheet1").Range("A1").Value

This is essentially adding an equals sign. If you want to do this for many cells, you can do something like so:

Sub ConvertToFormula()
Dim rngCell As Range
For Each rngCell In Worksheets("Sheet1").Range("A1:Z100")
    If rngCell.Value <> "" And Not rngCell.HasFormula Then
        rngCell.Formula = "=" & rngCell.Value
    End If
Next
End Sub

This will currently convert any cell within Range A1:Z100 that is not blank and does not have a formula.

 

[DushiPunda]

Alternatively, we can use evaluate:

Sub ConvertToFormulaTest()
Dim prevValue As Variant
Dim rngCell As Range
For Each rngCell In Worksheets("Sheet1").Range("A1:Z100")
    If rngCell.Value <> "" And Not rngCell.HasFormula Then
        prevValue = rngCell.Value
        rngCell.Value = Evaluate(rngCell.Value)
        If IsError(rngCell.Value) Then rngCell.Value = prevValue
    End If
Next
End Sub

So this will likely be the better way of handling this. Anything that produces an error from the evaluate line will get reset to the previous value...so if there are cells that contain text, they will remain unchanged. Anything that is evaluate-able, such as your fractions, will get changed to decimal form with the evaluate line. Hopefully that makes sense.

This is still only looking at A1:Z100 on Sheet1...you'll need to adjust those accordingly.

 

[ouadad]

I used the last suggestion and it worked perfectly. Thank you very much.