I encountered a situation recently where I needed to convert a text string into a formula for calculation. I was expecting to find Excel would have the ability to do this. And it does . . . in part.
If you have a number stored as text you can multiply it by 1 and it will be converted to a number. But what if we want to convert a whole text string into a formula, rather than just converting a single number? How can we achieve this? Excel has the EVALUATE formula, however, this is a legacy formula which does not quite function how you might Expect. So, let’s see how we can solve this.
Using EVALUATE to convert string to a formula
Let’s use an easy example of how the EVALUATE function might convert a string into a formula.
In our worksheet we have data in 3 cells
A1 = 1
A2 = +
A3 = 1
If we wanted to use this information to create the formula 1+1 (which equals 2), how could we do it? The EVALUATE formula would be the perfect option, except it only partially exists. If we were to include a formula such as:
=EVALUATE(A1&A2&A3)
Note: The & is used to combine cells together into a single string, so our example would become:
=EVALUATE(“1+1”)
It seems like it might work, but if we try, we will receive an error like this:
However, we can create that same formula as a named range.
Formulas -> Define Names -> Name Manager
Click New
Enter the formula in the Refers To box and click OK.
That named range is now available for use in formulas.
The result in Cell C1 will become: 2
This might be useful to a certain extent, but in most circumstances it does not provide a suitable solution for converting a text string to a formula. We need something which gives the same result, but which can be used as a normal Excel formula.
EVALUATE as a User Defined Function to convert string to formula
It is possible to create our own Excel functions with VBA; these are known as a User Defined Functions (UDF). These work the same way as Excel’s standard functions, but we determine how the result should be calculated.
If we insert the following VBA code into a Module of the Visual Basic Editor we can create our own EVALUATE formula.
Function EvaluateString(strTextString As String) Application.Volatile EvaluateString = Application.Caller.Parent.Evaluate(strTextString) End Function
That’s it. Just 4 lines. Even though Excel does not have the EVALUATE function itself, VBA still does.
We are now able to use this as if it were a normal function.
The result in Cell C1 will become: 2
There is no need to create a named range each time, and this formula is now available for use in our workbook.
Note: If the code is not placed in a Module it will not work.
Conclusion
To convert a text string to a formula it appears the VBA User Defined Function route is the best option. Whilst it is possible to use a named range, it is restricted by what it can achieve.
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.