Evaluate a Text Equation

By using an old Excel4 macro function called EVALUATE in a special way, you can easily evaluate text equations (an equation formatted as text and interpreted as such by Excel) as actual calculations.

Sometimes you might want to evaluate an equation that is formatted and interpreted as text by Excel, such as 20+67+88+23+30. If these numbers were typed into a cell with no equals (=) sign, Excel would read them as text and they would be treated as such with regards to calculation. But with this tutorial, we can get Excel to evaluate the equation anyway.

Set up your spreadsheet like the one shown in Figure below, ensuring that none of your formulas have an equals sign (=). This will ensure that Excel treats them as text.

In our example, we want to leave the original cell contents intact and use Column B to return the result of the equations.

Click in cell B1 and try the usual suspect of = "="&A1. You will see that the results in B1 only show =20+67+88+23+30, rather than evaluating the formula. We need to apply the EVALUATE function to get the results we want.

Again, click in cell B1 and enter the following formula:

=EVALUATE(A1)

Excel won't like this and will return the error message "That function is not valid." But we can force it to be valid. Click in cell B1 and select Formulas → Defined Names → Define Name (pre-2007, Insert → Name → Define). In the Names: box, type Result (or any valid range name), and type =EVALUATE($A1) in the Refers to: box. Click OK.

You must select cell B1 and used a relative rowreference for $A1.

Now click in cell B1, enter =Result, and copy down to B5.

Excel won't like this and will return the error message "That function is not valid." But we can force it to be valid. Click in cell B1 and select Formulas → Defined Names → Define Name (pre-2007, Insert → Name → Define). In the Names: box, type Result (or any valid range name), and type =EVALUATE($A1) in the Refers to: box. Click OK.

You must select cell B1 and used a relative rowreference for $A1.

Now click in cell B1, enter =Result, and copy down to B5.