45

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.

4
  • 3
    excel had this EVALUATE() function that does exactly this. That was a long time ago though, and i am not too sure about the new excel. Will poke around and see if i can find something equiv.
    – aCuria
    Mar 5, 2011 at 1:12
  • 1
    that function sounds familiar, but I certainly can't find it in Excel2007, which is what I'm currently using.
    – drapkin11
    Mar 5, 2011 at 1:15
  • I cant find it either =/
    – aCuria
    Mar 5, 2011 at 1:17
  • 2

8 Answers 8

32

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

2
  • 1
    I forgot all about user-defined functions in Excel -thanks.
    – drapkin11
    Mar 5, 2011 at 3:21
  • I did a small modification changing Range parameter to String and it works nice form me. thanks
    – Makah
    Jan 20, 2014 at 20:26
18
=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)

2
  • 1
    Very good - and nice to know. It even works for a table column: =EVALUATE(Tablename[@[column]]) Something you can't find in an most Excel Help- or advanced Tutorial easily
    – Paschi
    Oct 11, 2017 at 20:13
  • 1
    ... and as usual with excel formulas, if you use a localized build, you need to use the localized function - e.g. in Excel 2016 DE it's called =auswerten(...)
    – kiwiwings
    Sep 4, 2018 at 13:28
13

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

4
  • Thanks I have been looking for a trick like this of and on for some time :) Feb 15, 2019 at 16:22
  • Another cure is to use the formula =EVALUATE(GET.CELL(5,OFFSET(INDIRECT("RC",FALSE),0,-1))) from this answer.
    – ZygD
    Mar 22, 2021 at 9:28
  • 2
    @jlear's solution is the best answer as it does not require VBA and allows you to use references in your text formula without the recalculation issue. Note the use of relative referencing (A1 not $A$1 when defining the name WHILE your cursor is positioned at B1). So again, with cell B1 selected define name "EvalLeft" as =IF(TODAY(), EVALUATE(A1)), and set the scope to sheet1 (and redefine it in every sheet you want to use it, otherwise with scope set it workbook the relative reference will always look to sheet1).
    – Mobus
    Mar 24, 2021 at 0:38
  • 2
    UPDATE: Also with Office 365 you can now define named functions using the lambda function =LAMBDA(x, if(today(),evaluate(x),)) and call it in the workbook like so =eval(C1). No need to fiddle with relative named ranges.
    – Mobus
    Apr 12, 2021 at 22:43
6
=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.

2
  • Doesn't work, returns #REF
    – SIslam
    Jan 11, 2016 at 5:29
  • 11
    INDIRECT can perform math and functions, but only as part of creating a cell reference. It can't be used in the general sense asked in the question.
    – fixer1234
    Sep 7, 2016 at 16:14
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
    
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.

1
  • Nope. Note the use of relative references in the defined named range. If you used $A$1 in the name manager it will be locked to that cell. However, if you put your cursor in B1 and define the name "OneCellLeft" as =A1 then OneCellLeft is relative and will always return the cell value to the left, relative to the cell the named range is being used in. This is a very powerful to achieve "OFFSET" like functionality without using OFFSET, which is volatile and potentially slow in large workbooks.
    – Mobus
    Mar 24, 2021 at 0:08
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.

1
  • 1
    To be clear, if you use references in your text string formula e.g. "A1 + B1" as text in C1 and then EVALUATE that string (in say D1 with the named range), Excel will not know that the answer to cell D1 depends on A1 and B1. It will only recalculate D1 if the contents of cell C1 changes - that is if the string formula itself changes. In this case @jlear's solution is CORRECT and the use of the volatile TODAY() function must be included in the defined name.
    – Mobus
    Mar 24, 2021 at 0:54
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.