Showing excel formula as text and evaluating text as formula

Evaluating text as a formula

Excel does not have an inbuilt function to evaluate texts written in cells as formulas. Such cases arise, for example, when you want to show the calculation steps and not only the final answer.

This issue can be solved by using a VBA function, as shown below. The function takes the text where the formula is written and then evaluates it using the power of VBA.

Function EvaluateFormula(Formula As String) As Variant
           EvaluateFormula = Evaluate(Formula)
End Function

For example, if A1 cell contains text (12+2*4) then EvaluateFormula (A1) function will give the value as 20.

Extracting text from the formula

On the other hand, it may also be useful if you want to extract the formula from the cell. Fortunately, for such case, there exists an inbuilt function in Excel. It can be done using FORMULATEXT() function.

For example, the following will give the formula used in A1 cell as a string. We use MID() function to remove “=” sign from the formula.

=MID(FORMULATEXT(A1),2,LEN(FORMULATEXT(A1)))