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

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0210 Convert text to formula.zip

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.

Text to convert to formula

In cell C3 we have the text 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.

EVALUATE as worksheet formula

The formula in C5 is:

=EVALUATE(C3)

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

EVALUATE Error

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:

EVALUATE in name manager
  • Name: fxTextToFormulaXL4
  • Refers to: =EVALUATE(C3)
  • Click OK

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

EVALUATE result as named range

The formula in cell C5 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.

fxTextToFormula LAMBDA EVALUATE Volatile - convert text to a formula
  • 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.

LAMBDA EVALUATE returns result

The formula in cell C7 is:

=fxTextToFormula(C3)

It returns the value of 2.

It works! AMAZING!

IMPORTANT

  • The LAMBDA function is only available for Excel 365.
  • Using EVALUATE requires the workbook to be saved as a .xlsm (Macro-enabled workbook).
  • It is unlikely that EVALUATE will ever be supported in Excel Online.

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.

Insert Module into VBA

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.

VBA UDF result

The formula in cell C9 is:

=fxTextToFormulaUDF(C3)

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?

Function as Text

The value in cell C3 is an INDEX/MATCH function combination as text.

Cell C5, C7, and C9 now recalculate and show the correct value.

IMPORTANT:

The original EVALUATE in cell C5 is not volatile:

  • If the value in C3 changes the formula recalculates
  • If the values in the Table change, it does not recalculate
  • As a result, C5 may show the incorrect result.

When setting up the LAMBDA/EVALUATE and UDF versions, we created volatile formulas. Therefore, these should always show the correct result.

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:


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

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

12 thoughts on “How to convert text to a formula in Excel (3 ways)”

  1. Thanks Gary.

    I still find it odd that there is not a native function in Excel which does this. If there is we’ve obviously both missed it. 🙂

    Reply
    • Hi Nat – I’ve re-tried the examples, and they still work. It might be worth checking that you’ve applied it correctly.

      Reply
      • I know this may be a little late, but this only thing that I would suggest is that it has cannot be done as a range and each cell selected separately i.e A1, A2, A3, and not A1:A3 .. That may help.

        Awesome work by the way. it works for me on the simpler codes. but am trying to force through a calculated string as a formula. I have managed to make a calculated variable formula that is a sumproduct, so that I can get the values from various files. almost there just needing to get my string of =sumproduct(…..) back as a normal formula output. Maybe it’s just impossible. but I feel its worth a shot. (doing this as a workaround of =sumproduct(Indirect(…)) as this doesn’t work.

        Reply
  2. OK, but if I build a formula into a SQL Query and a field of resultset contains a string that is a formula. How do I get it recognized by Excel?
    Thanks

    Reply
    • LAMBDA is still on the Beta Channel only at the moment. So we’ll see if Microsoft decides to make any further changes before taking it live.

      Thanks for providing the link, that’s a useful resource.

      Reply
    • Hi Charles – Thank you for leaving a comment. That’s a really useful post, I think I’ll need to look into EVALUATE a bit mote.

      I have updated mine to take your recommendations into account.

      Reply
  3. Any thoughts on getting the vba to work with table references instead of cell references?

    In other words, if cells A1, A2, and A3 were in a table with columns named Num1, Sum, Num2, and a column that uses =EvaluateString([@[Num1]]&[@[Sum]]&[@[Num2]] to calculate a larger set of data

    Reply
    • Are you sure you need to do it that way? In that scenario, you should be able to retrieve the entire column using an INDEX function.

      Reply

Leave a Comment