How to convert text to formula in Excel

Posted on by

We know an Excel function “FORMULATEXT” which converts Formula into Text. What if you want to do the reverse i.e. convert excel text into active formula?

Problem statement

Let’s say your Excel cell has following string or text.

A1+B1

Now you want to convert the above text into active formula that evaluates it instead of just printing text.

Solution

Excel doesn’t have a built-in function that directly convert text strings to active formulas, However, using a simple VBA script, you can create a custom function that does the same.

The following VBA script converts the text or string into an active formula that gets actually evaluated.

Function Eval(ByVal formulaText As String) As Variant
    On Error Resume Next
    Eval = Evaluate(formulaText)
End Function

To use the above VB code in Excel Workbook, follow these steps

  1. Right click the sheet name and click “View Code“
    open VBA application to add module.
  2. Go to Insert > Module
  3. Copy the above VB code and paste it as shown below.
    Insert Eval code in Excel to convert text to formula
  4. Press “Ctrl + S” to save it as Macro Enabled Workbook so that you can use this formula.
    Save VBA function as macro enabled worksheet

Presently this formula is only available in present sheet. To make it available in all Excel sheets follow steps provided here.

Use Eval function defined above to evaluate or convert text into active formula.

  • Go to cell where you want to get evaluate text as formula.
  • type “=Eval(<cell containing text formula>)” and hit enter. Inside Eval function, refer cell containing text formula which you need to convert into active formula in Excel.