How to convert text to a formula in Excel (3 ways)

I encountered a situation recently where I needed to convert text to a formula in Excel. I was expecting to find a function that would achieve this.

We can convert text to a range using the INDIRECT function, so there has got to be a function to convert text to a formula… right?

Well… no… yes, kind of!

In this post, we look at 3 options for converting text into a formula.

Table of Contents

• Using EVALUATE to convert text to a formula
• Using EVALUATE inside a LAMBDA
• Convert string to formula with EVALUATE inside VBA
• What about more complex calculations?
• Conclusion

Watch the video

Watch on YouTube

Using EVALUATE to convert text to a formula

Excel still has the legacy EVALUATE function. It is an Excel 4 Macro, which means it hasn’t been updated or considered part of the main product since the mid-1990s. But it still works!

As EVALUATE is a special type of function, it behaves differently from other worksheet functions. Let’s see how to use it.

The syntax for EVALUATE is:

=EVALUATE(text)

The text is calculated as a formula, and the result returned.

Let’s look at a simple example to demonstrate how to use EVALUATE to convert text to a formula.

In cell C5 we have a formula

=CONCAT(C3:E3)

This returns the text of 1+1

We want to calculate this text and return the value 2.

We can use all the text functions at our disposal to create the text string required.

If EVALUATE were a standard Excel function, it would be perfect.

The formula in C7 is:

=EVALUATE(C5)

It seems like this should work. But as soon as we commit the formula, it leads to an error. Oh No!

As mentioned above, EVALUATE is not a standard Excel function. It is an Excel 4 Macro function. We can use these inside a name, but not on the worksheet.

From the ribbon, click Formulas > Name Manager.

Then, in the Name Manager dialog box click New.

In the New Name dialog box enter the following:

• Name: fxTextToFormulaXL4
• Refers to: =EVALUATE(C5)
• Click OK

We can now use the name to refer to the formula result.

The formula in cell C7 is:

=fxTextToFormulaXL4

This might be useful to some extent. But:

• It doesn’t really provide the flexibility we want for the real world.
• We would need to create a named range for every calculation.

The formula is not volatile and does not always recalculate.

We address all of these issues in the next section.

Using EVALUATE inside a LAMBDA

We have seen that EVALUATE works if held inside a name. This reminds me of another function that works if held inside a name… LAMBDA.

LAMBDA is a function that creates functions. And, we create those functions inside a name.

So, let’s create another name, and this time we will use EVALUATE inside a LAMBDA.

• Name: fxTextToFormula
• Refers to: =LAMBDA(string,EVALUATE(string&T(NOW())))
• Click OK

We are dealing with static text values; therefore, it does not know if any cells referred to by the text have changed. Which means, results will not always update when values change. To force recalculation, we make the formula volatile by adding &T(NOW()).

We can now use the LAMBDA as a standard function.

The formula in cell C9 is:

=fxTextToFormula(C5)

It returns the value of 2.

It works! AMAZING!

Convert string to formula with EVALUATE inside VBA

It is possible to create our own Excel functions with VBA; these are known as User Defined Functions (UDFs). Once created, we can use these like standard Excel functions.

Since this method uses VBA, it works wherever VBA exists. So, it works with older versions of Excel, but not with Excel Online.

Press Alt + F11 to open the Visual Basic Editor.

In the VBA Project pane, select the workbook in which you wish to create the function. Then click Insert > Module.

In the code module enter the following:

Function fxTextToFormulaUDF(text As String)
Application.Volatile
fxTextToFormulaUDF = Application.Caller.Parent.Evaluate(text)
End Function

That’s it. 4 lines of code is all we need. In this code, we pass the string into VBA’s Evaluate function, and return the result.

Close the Visual Basic Editor.

We are now able to use this function as if it were a normal function.

The formula in cell C11 is:

=fxTextToFormulaUDF(C5)

It calculates the correct result… AMAZING!!

What about more complex calculations?

So far, we have only used 1+1, hardly the most taxing of scenarios.

What if we enter a function? Will that work?

In the screenshot above there is:

• Table called Data
• Criteria cells G15:I17
• FILTER function generated as text G20

Cell G20 contains the following formula:

="=FILTER(Data,"&TEXTJOIN("*",TRUE,IF(G15:G17="","",
"(Data["&G15:G17&"]"&H15:H17&I15:I17&")"))&")"

We will not go through this formula in detail, but it creates dynamic text:

• If the Criteria is Value > 15 the text result is =FILTER(Data,Data[Value]>15))
• If the Criteria is Value > 15 and Value <= 20 the text result is =FILTER(Data,Data[Value]>15)*Data[Value ]<=20))
• If the Criteria is Value > 15 and Value <= 20 and Region = “South” the text result is =FILTER(Data,Data[Value]>15)Data[Value]<=20)Data[Region]=”South”))

We can use this text inside one of our text to formula functions.

The formula in cell G23 is:

=fxTextToFormula(G20)

The result of the text FILTER function spills into the remaining cells.

This shows that we are not restricted to simple formulas but can handle reasonably complex situations.

Conclusion

Excel does not have a function to evaluate a string as a formula. However, as shown in this post, using a LAMBDA/EVALUATE combination or a VBA User-Defined Function, we can quickly create our own formulas.

Related Posts:

• Dynamic arrays and VBA user defined functions (UDFs)
• Using Excel 4 Macro Functions

---

What next?

Discover how you can automate your work with our Excel courses and tools.