Mass conversion of equations to excel formulas

[unclefrank58]

I deal with equations that are drafted in microsoft word that look like this:

(1.0 x 0.03125 x 0.20 x 187.4237/1920.00)

If I independently put an = sign in front of (1.0 it will turn it into a formula after asking to correct for typo for x to become *

The problem is I have hundreds of these equations and I was wondering if there is a way to turn all the equations into formulas with the answer without doing it individually.

Any ideas?

 

[unclefrank58]

I have tried using the formula again and it works on converting the equations into formulas but it now changes the headings into #NAME ? and all blank cells into an = sign.

For example:

John Doe

(1.0 x 1.0 x 0.00234 x 0.785 x 319.63/319.63) becomes #NAME ? 0.0018369 = Anyway to keep it from changing blank cells and cells with names only? Thanks in advance.

<colgroup><col></colgroup><tbody>
</tbody>

 

[Joe4]

Please post the exact code you are using that causes this.
Will your headers always be in row 1?
Is that the only place they will be?

 

[unclefrank58]

Please post the exact code you are using that causes this.
Will your headers always be in row 1?
Is that the only place they will be?

Sub ConvertFormulas()

Dim frm As String
Dim cell As Range

Application.ScreenUpdating = False

' Loop through all cells in selection
For Each cell In Selection
frm = "=" & Trim(Replace(cell, " x ", " * "))
cell.Formula = frm
Next cell

Application.ScreenUpdating = True

End Sub


the names and formulas will all be in the same column.

John
(1.0 x 0.4 x 80/80)

Sam
(1.0 x 0.2 x 80/80)

Jake
(1.0 x 0.4 x 80/80)

Hope that makes sense. the formula trips up on the names and adds an = sign before them, I only want an equal sign when there is a parentheses at the beginning of the cell.

 

[Joe4]

If all the formulas that you want to convert begin with a "(", we can work it in our code to only convert those, i.e.

Sub ConvertFormulas()

    Dim frm As String
    Dim cell As Range

    Application.ScreenUpdating = False

'   Loop through all cells in selection
    For Each cell In Selection
        If Left(cell, 1) = "(" Then
            frm = "=" & Trim(Replace(cell, " x ", " * "))
            cell.Formula = frm
        End If
    Next cell

    Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

If all the formulas that you want to convert begin with a "(", we can work it in our code to only convert those, i.e.

Sub ConvertFormulas()

    Dim frm As String
    Dim cell As Range

    Application.ScreenUpdating = False

'   Loop through all cells in selection
    For Each cell In Selection
        If Left(cell, 1) = "(" Then
            frm = "=" & Trim(Replace(cell, " x ", " * "))
            cell.Formula = frm
        End If
    Next cell

    Application.ScreenUpdating = True

End Sub

If that does what the OP wants, then the code I posted in Message #6 should also do it, just replace the Columns(1) I used there with Selection.