Skip to content

Evaluates String as if it Were a Formula in Excel

Microsoft Excel has vast library of built in function, but there are some tasks that it doesn’t handle out of the box. One such task is evaluating a formula stored as a text string within a cell. Though Excel doesn’t provide a direct function for this, you can leverage the power of VBA (Visual Basic for Applications) to create a function that evaluates a string as if it were a formula. In this blog post, we will guide you through creating and using this custom function in Excel.

Problem Statement

For example, you might have a cell containing the text “SUM(1, 2, 3)” or “=SUM(1, 2, 3)” and you want Excel to display the result of this formula as 6 in another cell. This is just an example, instead you can have any other formula as string.


There is no direct formula that does the above work but with a simple VBA function, you can achieve this functionality very easily.

Step by step guide

1. Open the VBA Editor

  • Go to Developer Tab > Visual Basic or Press <strong><em>Alt + F11</em></strong> to open the VBA editor. Note: If you don’t see Developer Tab follow these steps.
    open visual basic in Excel
  • Insert a new module by right-clicking on any existing module or the workbook name, then selecting Insert > Module.
    Insert new module in Excel VB

2. Add the following VB code

Function EvaluateString(FormulaCell As Range) As Variant
    EvaluateString = Evaluate(FormulaCell.Value)
End Function

Code Explanation:

  • FormulaCell As Range: This allows the function to take a cell reference as an argument.
  • Application.Volatile: This ensures that the function recalculates every time a change occurs in the workbook, making it dynamic.
  • Evaluate(FormulaCell.Value): This evaluates the text string in the referenced cell as a formula and returns the result.

3. Save code and Close VB editor

4. Use above VB function to evaluate String as if it was Excel formula

Let us understand its use with following Example

  • In cell A1, enter the formula string you want to evaluate, such as "=SUM(1, 2, 3)".
  • In another cell (e.g., B1), use the custom function =EvaluateString(A1) and hit enter
  • Cell B1 will now display the result of the formula in A1, which in this case is 6.
    Example of how to evaluate string as if it were formula

Get Developer Tab in Excel

To get developer tab

  1. Right click in empty area of the Ribbon
  2. Click “Customise the Ribbon” to open
    customize the ribbon in Excel
  3. In Customise the Ribbon, select Main Tabs, then Check the box against Developer and click OK.
    add developer tab in Excel
  4. Now you will see Developer Tab in Excel


Microsoft Excel, doesn’t have a direct formula that can evaluate string as if it is formula. However this can be easily achieved using VB function.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *