This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Turn a string into a formula with Evaluate

turn a string into a formula with evaluate

turn a string into a formula with evaluate

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

simple example 1 + 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:

evaluate Excel function will error

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:

error message function not valid

However, we can create that same formula as a named range.

Formulas -> Define Names -> Name Manager

location of the name manager in the ribbon

Click New

name manager window new name


Advertisement:

Enter the formula in the Refers To box and click OK.

evaluate example as a named range

That named range is now available for use in formulas.

evaluate example named range

The result in Cell C1 will become: 2

result of evaluate example named range

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.

evaluate string function - created as udf

The result in Cell C1 will become: 2

result of evaluate example named range

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.

Save