Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?

This would be helpful for another project where I would like to be able to write 'dynamic' formulas in Excel.

Solution 1:

EVALUATE is available in VBA in all current versions

You can include it in you VBA code, or wrap it into a simple UDF to make it available as a worksheet function

Function ev(r As Range) As Variant
    ev = Evaluate(r.Value)
End Function

It basically treats the value of the passed parameter as an Excel formula, same as if it were entered in a cell

"11+5" and "=11+5" will produce the same result

Solution 2:

=evaluate(put_reference[s]_here)

This is a semifunction - it can only be used in Name Manager.

This is how you can use it:

• Point to a cell and you open Name Manager (From the FORMULAS tab or by clicking CTRL+F3)

  

• Write =evaluate( and click on the cell you want (best to keep relative reference).

• Finish the formula with )

• Give it a NAME - (in this example I'll just call it eva).

• Click OK.

Now, let's suppose that you've selected B1 and made all this refer to A1. In A1 you can put "1+1" and in B1 you write =eva - once you've hit ENTER, the B1 value will be 2. As the reference in Name Manager was relative, you can use =eva to get the evaluation of any cell one cell left from where you want it. (eg. in B2, =eva will return the result of cell A2)

Solution 3:

There is an important caveat with the great answer from @karel and @Laurentiu Mirica: the evaluate function won't recalculate unless the referenced cell changes. For example, cell C1 contains the text "A1+B1" and D1 contains the function =eval. If the values in A1 or B1 change, the cell D1 does not get recalculated.

This can be corrected by introducing a volatile function into either the string or the eval cell. This will force a recalculation every time the worksheet is recalculated. For example, cell C1 could be replaced with =if(today(),"A1+B1",). Or, D1 could be replaced with =if(today(),eval,). Any volatile function should do.

A third and perhaps the simplest solution is to change semi-function in the name manager to =if(today(),evaluate(c1),)