Compute string content

D

David Biddulph

Put this formula in the cell:
=SQRT(2)*(5-2)
and you should get a result like 4.242641
 
J

joeu2004

How can I compute in a cell an expression string such as "sqrt(2)*(5-2)" ?
Click to expand...

Not sure exactly what you mean.

First, do you actually have the quotes ("...") in the cell; or do you
have just the text without the quotes?

In either case, the "obvious" answer is: remove the quotes ("..."),
if they are present, and insert an equal sign (=) on the left. In
other words, manually turn the text into a formula.

But if what you mean is: you an expression as text in a cell, and in
another cell, you would like to evaluate the text as an Excel formula,
perhaps the following UDF would be helpful:

function doit(rng as Range)
s = replace(rng.value, chr(34), "")
doit = evaluate(s)
end function

Usage: If A1 contains the expression as text:

=doit(A1)

Note: The Replace() function call is needed only if you might have
explicit quotes in the string expression. If not, remove the "s="
line and "s" with rng.value in the Evaluate() function call.

Use alt+F11 and Insert>Module to enter the text as in the VB Editor.
 
G

Gord Dibben

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Get rid of the quote marks then in a cell enter =evalcell(cellref)

Your example returns 4.242640687


Gord Dibben MS Excel MVP
 
J

jfkaufeler

Thank you for your prompt anwer. I didn't formulate my problem correctly.
What is in the cell is an expression string which was itself computed from
other cells (e.g. concatenation of different cell content (values and
operators).
Regards,
 
J

jfkaufeler

Many thanks, it looks to answer my question but I didn't have time to try.
Regards,
 
Top