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
Watch the video
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!
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.
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:
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.
Thanks, that is a life saving tip
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. 🙂
Mine just returns 0, not even the example works, zero is all I get with the 🙁
Hi Nat – I’ve re-tried the examples, and they still work. It might be worth checking that you’ve applied it correctly.
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.
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
Note, with LAMBDA, you can reference EVALUATE from a sheet without VBA; still have to save workbook as xlsm though. See here: https://techcommunity.microsoft.com/t5/excel/legacy-evaluate-in-lambda-build-test-lambdas-from-sheet/td-p/2133924
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.
There are a lot of limitations and quirks associated with using VBA Evaluate.
In particular you should use Worksheet.Evaluate rather than Application.evaluate, or in your VBA UDF use Application.Caller.Parent.Evaluate(strTextString).
See https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/ for some Evaluate limitations
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.
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
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.