54

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.

Lance Roberts
  • 8,837
drapkin11
  • 1,014

8 Answers8

34

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

phuclv
  • 30,396
  • 15
  • 136
  • 260
chris neilsen
  • 4,383
26
=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)

    Evaluate Example

  • 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)

Community
  • 1
Laurentiu Mirica
  • 371
15

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.

Demonstration of eval problem

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),)

jlear
  • 161
9
=indirect()

if you use this in a cell (alongside concatenate) it can be very useful.

For example, this formula will display the value of cell B5 on another worksheet (the name of which is stored in cell A2 on this worksheet):

=INDIRECT(CONCATENATE(A2,"!B5"))

To make INDIRECT work the external worksheet must be open.

Meir Bar-Tal
  • 3
user206351
  • 115
2

A 2021 footnote – the picture changes a bit with the introduction of the LAMBDA function (in Office 365 betas at the time of writing), which can pass arguments from a worksheet cell to the Name Manager evaluate function.

A minimum example: for a user-defined EVAL function, available to cell expressions, we could make the following name binding in Name Manager:

EVAL
=LAMBDA(s, evaluate(s))
houthakker
  • 121
1

Enhanced Function to execute a string as though it is a formula. Function extended from above ev() function.

New Function Name: EvaluateEx()

  • Paste function to an Excel VBA Module.
  • Place function name into a worksheet cell.

Example Calling Syntax: * =EvaluateEx("=11 + 5") * =EvaluateEx("=g1 + g2")+ evaluateEX("2 + 3") * =EvaluateEX("defined name")

Function tested with:

  • Structured Table References

  • Defined Names that reference text,
    filter() function, etc.

    Function evaluateEx(r As Variant) As Variant
'Note: Renaming function requires same change after the ExitFunction label.
'User Function to evaluate string as formula.
 Dim ev As Variant

   Select Case TypeName(r)

       Case "Range"
            If r.Value <> vbNullString And Trim(r.Value) <> "=" Then
               ev = Evaluate(r.Value)
            Else
               ev = r.Value
            End If

       Case "String"
            If r <> vbNullString And Trim(r) <> "=" Then
               ev = Evaluate(r)
             Else
               ev = r
             End If

       Case "Variant()"
             ev = r

       Case "Double"
             ev = r

       Case "Error"
             ev = "Defined Name not found in list of Defined Names. Cannot Evaluate"

       Case Else
             ev = "Unknown parmeter type. Cannot Evaluate"
    End Select

    On Error GoTo ExitFunction 'Handle possible type mismatch
    If ev = CVErr(2029) Then
             'ev = "The parameter passed to the EV function results in a value (i.e. " & r & ") that cannot be evaluated by the EV function."
              ev = r
    End If

ExitFunction:

    evaluateEx = ev

End Function
Gary Rosner
  • 41
1

All other answers which employ EVALUATE from Excel 4.0 only show how to evaluate the text in the absolute cell reference. E.g., the formula will always evaluate the contents of cell A1 and in order to evaluate the contents of another cell one would need to create a separate named range with a different absolute path.

This will show how to employ GET.CELL to make the EVALUATE look one cell to the left.

Formulas -> Name Manager -> New...
Name: EVAL_ON_LEFT
Refers to: =EVALUATE(GET.CELL(5,OFFSET(INDIRECT("RC",FALSE),0,-1))) Edit Name dialog

Result:
enter image description here

This formula is better than the absolute reference alternative also because it does not have recalculation problem.

ZygD
  • 2,577
0

jlear's answer is INCORRECT regarding Laurentiu Mirica's answer using the Excel 4 Macro function EVALUATE() in a Named Range: it will recalculate EVERY TIME one of the inputs changes. Which makes it a wonderful solution to exactly this kind of problem.

Of course, one can simply use A1 and B1 in the formula, if that is where the values are. It is NOT required that everything be numerals in a string in a single cell. Though it works for that too.

jeorje
  • 1