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)

Advertisement:

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

Advertisement:

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.

Advertisement:

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