Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?

This would be helpful for another project where I would like to be able to write 'dynamic' formulas in Excel.

share|improve this question
    
excel had this EVALUATE() function that does exactly this. That was a long time ago though, and i am not too sure about the new excel. Will poke around and see if i can find something equiv. –  aCuria Mar 5 '11 at 1:12
    
that function sounds familiar, but I certainly can't find it in Excel2007, which is what I'm currently using. –  drapkin11 Mar 5 '11 at 1:15
    
I cant find it either =/ –  aCuria Mar 5 '11 at 1:17
add comment

3 Answers

up vote 8 down vote accepted

EVALUATE is available in vba in all current versions

You can include it in you vba code, or wrap it into a simple udf to make it available as a worksheet function

Function ev(r As Range) As Variant
    ev = Evaluate(r.Value)
End Function

It basically treats the value of the passed parameter as an excel formula, same as if it were entered in a cell

"11+5" and "=11+5" will produce the same result

share|improve this answer
    
I forgot all about user-defined functions in Excel -thanks. –  drapkin11 Mar 5 '11 at 3:21
    
I did a small modification changing Range parameter to String and it works nice form me. thanks –  Makah Jan 20 at 20:26
add comment
=indirect()

if you use this in a cell (alongside concatenate) it can be very useful.

For example, this formula will display the value of cell B5 on another worksheet (the name of which is stored in cell A2 on this worksheet):

=INDIRECT(CONCATENATE(A2,"!B5"))
share|improve this answer
add comment
=evaluate(put_reference[s]_here)

This is a semifunction - it can only be used in Name Manager.

This is how you can use it: - you point to a cell and you open Name Manager - then you write =evaluate( and click on the cell you want [best to keep relative reference] - you finish the formula with ) - you give it a NAME - [in this example I'll just call it eva] - you click OK

Now, let's suppose that you've selected B1 and made all this refer to A1. In A1 you can put "1+1" and in B1 you write =eva - once you've hit ENTER, the B1 value will be 2. As the reference in Name Manager was relative, you can use =eva to get the evaluation of any cell one cell left from where you want it. (eg. in B2, =eva will return the result of cell A2)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.