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?

 

[Joe4]

Welcome to the Board!

Select all the cells you want to apply this to and run this VBA code:

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

 

[kweaver]

I tried it with a UDF:

Function EvalString(strTxtString As String)
Application.Volatile
strTxtString = Replace(strTxtString, "x", "*")
EvalString = Evaluate(strTxtString)
End Function

 

[Joe4]

I don't know if a UDF will do what they want here. Based on the following, it sounds like they want to turn it into an active formula:

I was wondering if there is a way to turn all the equations into formulas with the answer without doing it individually.

Using a UDF with the Evaluate function will return the result, not convert the entry into a valid formula, which is why I approached it the way I did.

 

[kweaver]

AH, right...if that's what they wanted versus the result only.

 

[Rick Rothstein]

If I understand what the OP wants correctly, and assuming the equations are in Column A, here is another macro that should work...

Sub MakeEquationIntoFormula()
  Columns("A").Replace "x", "*", xlPart, , False, , False, False
  Columns("A").Replace "(", "=(", xlPart
End Sub

 

[kweaver]

Rick, I was thinking somewhat along those lines but if there are double ((calculations)/whatever) won't that replacement will be a problem.
And, if the formula isn't surrounded by one open and one closed paren, there's a problem also, yes?

 

[Rick Rothstein]

Rick, I was thinking somewhat along those lines but if there are double ((calculations)/whatever) won't that replacement will be a problem.
And, if the formula isn't surrounded by one open and one closed paren, there's a problem also, yes?

Yes, but since the OP only gave a single example, and since I know absolutely nothing about his actual data, I have assumed his example is representative of all of his data. If that is not correct, he should show us a sampling of the type of data he actually has to process.

 

[unclefrank58]

Thanks excel masters, the first one worked for me. I really appreciate it. Saved me a lot of time.

 

[Joe4]

You are welcome.
Glad we were able to help!