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.

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.

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!

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(C3)* - Click
**OK**

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

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.

- 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 **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.**

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 **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?

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**

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.