Convert text to Formula in Excel 2007

Thanks:  0
Likes:  0

# Thread: Convert text to Formula in Excel 2007

1. ## Convert text to Formula in Excel 2007

Hello Forum,

I need to build interactive formulas as part of a bigger table.

I have come only this far with the test model:

A1 1 (or any value)
B1 + (or any operator)
C1 2 (or any value)
D1 =1+2 as the result of ="="&A1&B1&C1 in text format

Now I want E1 to show the result. In this case a total of 3

How can I convert D1 to a formula in E1 that calculates the result and changes with any of the input values? I have spend ours on it without results . Can something this simple be that complex; PLEASE

Sebastiaan

2. ## Re: Convert text to Formula in Excel 2007

Welcome to the Board!

Maybe try in E1:

=eval(d1)

or:

=eval(A1&B1&C1)

Hope that helps.

3. ## Re: Convert text to Formula in Excel 2007

Originally Posted by schielrn
Welcome to the Board!

Maybe try in E1:

=eval(d1)

or:

=eval(A1&B1&C1)

Hope that helps.

4. ## Re: Convert text to Formula in Excel 2007

Pretty sure you need VBA to do that.
Here's a basic UDF to do it. May need tweaking for building complex formulas in this manner. But works for the basic example you provided.

Put this code in any standard module
Code:
```Public Function Eval(myval As String)
'Evaluates a text string as A formula
Eval = Evaluate(myval)
End Function```
Then you can use this in a cell formula

=EVAL(A1&B1&C1)

5. ## Re: Convert text to Formula in Excel 2007

Ahhh. Thanks. I didn't think it was a native formula, but knew there was evaluate in vba, so figured it might be. But thanks for spotting that out.

6. ## Re: Convert text to Formula in Excel 2007

Depending on how complex your operands can be, you could use an if statement:

=IF(B1="+",A1+C1,IF(B1="-",A1-C1,IF(B1="/",A1/C1,A1*C1)))

7. ## Re: Convert text to Formula in Excel 2007

EVALUATE is available to Names.

(After selecting E1 per: proper relative addressing )
one could define a name Name: formulaResult RefersTo: =EVALUATE(Sheet1!\$D1)

Putting =formulaResult in a cell (in row1) will then return the desired result.

8. ## Re: Convert text to Formula in Excel 2007

The =Eval or Evaluate function looked very promising and have tried the functions standalone and in a named cell. Sad to say, neither works. They do not appear to be standard dunctions. I'll see if I can download the function somewhere.

I will try to find and download "MoreFunc Add-In" as suggested. Hope that goes smoothly. Back to GGLE..............

This is where I went:

Download creates a setup file. Closed Excel, started setup, followed the instructions and SUPER. Re-started Excel and I have a whole series of new functions on the Formula ribbon. EVAL was there and worked. Problem solved.

Thank you all!

Sebastiaan

Sebastiaan

9. ## Re: Convert text to Formula in Excel 2007

EVALUATE will not work when in a formula that is entered in a cell.
EVALUATE will work when part of the definition of a Name.

e.g. Define a name
Name: Test
RefersTo = EVALUATE("2")

then,
putting =EVALUATE("2") in a cell will return an error
putting =Test in a cell will return 2.

10. ## Re: Convert text to Formula in Excel 2007

Hi evrybody,
Does enybody knows similiar formula that works with reference on closed workbook?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•