How to turn a string formula into a "real" formula?

Question

I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?

Answer 1

Evaluate might suit:

http://www.mrexcel.com/forum/showthread.php?t=62067

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

Answer 2

I concatenated my formula as normal, but at the start I had '= instead of =.

Then I copy and paste as text to where I need it. Then I highlight the section saved as text and press ctrl + H to find and replace.
I replace '= with = and all of my functions are active.

It's a few steps, but it avoids VBA.

Answer 3

UPDATE This used to work (in 2007, I believe), but does not in Excel 2013.

EXCEL 2013:

This isn't quite the same, but if it's possible to put 0.4 in one cell (B1, say), and the text value A1 in another cell (C1, say), in cell D1, you can use =B1*INDIRECT(C1), which results in the calculation of 0.4 * A1's value.

So, if A1 = 10, you'd get 0.4*10 = 4 in cell D1. I'll update again if I can find a better 2013 solution, and sorry the Microsoft destroyed the original functionality of INDIRECT!

EXCEL 2007 version:

For a non-VBA solution, use the INDIRECT formula. It takes a string as an argument and converts it to a cell reference.

For example, =0.4*INDIRECT("A1") will return the value of 0.4 * the value that's in cell A1 of that worksheet.

If cell A1 was, say, 10, then =0.4*INDIRECT("A1") would return 4.

Answer 4

Just for fun, I found an interesting article here, to use a somehow hidden evaluate function that does exist in Excel. The trick is to assign it to a name, and use the name in your cells, because EVALUATE() would give you an error msg if used directly in a cell. I tried and it works! You can use it with a relative name, if you want to copy accross rows if a sheet.

Answer 5

I prefer the VBA-solution for professional solutions.

With the replace-procedure part in the question search and replace WHOLE WORDS ONLY, I use the following VBA-procedure:

''
' Evaluate Formula-Text in Excel
'
Function wm_Eval(myFormula As String, ParamArray variablesAndValues() As Variant) As Variant
    Dim i As Long

    '
    ' replace strings by values
    '
    For i = LBound(variablesAndValues) To UBound(variablesAndValues) Step 2
        myFormula = RegExpReplaceWord(myFormula, variablesAndValues(i), variablesAndValues(i + 1))
    Next

    '
    ' internationalisation
    '
    myFormula = Replace(myFormula, Application.ThousandsSeparator, "")
    myFormula = Replace(myFormula, Application.DecimalSeparator, ".")
    myFormula = Replace(myFormula, Application.International(xlListSeparator), ",")

    '
    ' return value
    '
    wm_Eval = Application.Evaluate(myFormula)
End Function


''
' Replace Whole Word
'
' Purpose   : replace [strFind] with [strReplace] in [strSource]
' Comment   : [strFind] can be plain text or a regexp pattern;
'             all occurences of [strFind] are replaced
Public Function RegExpReplaceWord(ByVal strSource As String, _
ByVal strFind As String, _
ByVal strReplace As String) As String

    ' early binding requires reference to Microsoft VBScript
    ' Regular Expressions:
    ' with late binding, no reference needed:
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    re.Global = True
    're.IgnoreCase = True ' <-- case insensitve
    re.Pattern = "\b" & strFind & "\b"
    RegExpReplaceWord = re.Replace(strSource, strReplace)
    Set re = Nothing
End Function

Usage of the procedure in an excel sheet looks like:

Answer 6

In my opinion the best solutions is in this link: http://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function

Here is a summary:

1. In cell A1 enter 1,
2. In cell A2 enter 2,
3. In cell A3 enter +,
4. Create a named range, with =Evaluate(A1 & A3 & A2) in the refers to field while creating the named range. Let's call this named range "testEval",
5. In cell A4 enter =testEval,

Cell A4 should have the value 3 in it.

Notes:
a) Requires no programming/VBA.
b) I did this in Excel 2013 and it works.

Answer 7

Say, let we have column E filled by formulas that returns string, like:

= " = " & D7

where D7 cell consist more complicated formula, that composes final desired result, say:

= 3.02 * 1024 * 1024 * 1024

And so in all huge qty of rows that are.

When rows are a little - it just enough to copy desired cells as values (by RMB)
to nearest column, say G, and press F2 with following Enter in each of rows.
However, in case of huge qty of rows it's impossible ...

So, No VBA. No extra formulas. No F&R

No mistakes, no typo, but stupid mechanical actions instead only,

Like on a Ford conveyor. And in just a few seconds only:

1. [Assume, all of involved columns are in "General" format.]
2. Open Notepad++
3. Select entire column D
4. Ctrl+C
5. Ctrl+V in NPP
6. Ctrl+A in NPP
7. Select cell in the first row of desired column G1
8. Ctrl+V
9. Enjoy :) .

Answer 8

Excel 2019 here. EVALUATE isn't valid.

It would work if we created a Named Range out of it:

But in this case we provide an absolute reference, which is not nice:

1. We would have to modify the formula every time we want to reuse it.
2. When the value in A1 changes, the evaluated result would not change.

Solution:

=EVALUATE(GET.CELL(5,OFFSET(INDIRECT("RC",FALSE),0,-1)))

Answer 9

The best, non-VBA, way to do this is using the TEXT formula. It takes a string as an argument and converts it to a value.

For example, =TEXT ("0.4*A1",'##') will return the value of 0.4 * the value that's in cell A1 of that worksheet.