How to evaluate the text formula,eg.600*600*12

[leezhihong]

I want to evaluate C5:C15 in D5:D15, to get the solutions like E5:E15.
How to do?

Excel Workbook
	C	D	E
4		=?
5	600*600*12		4320000
6	600*600*17		6120000
7	500*600*2		600000
8	600*640*20		7680000
9	700*500*20		7000000
10	550*480*12		3168000
11	450*480*2		432000
12	550*480*16		4224000
13	350*350*15		1837500
14	320*320*12		1228800
15	250*250*2		125000
Sheet1

 

[AlphaFrog]

Perhaps someone has a more clever solution but this is one way. You could use a custom function...

• Alt+F11 to open the VBA editor
• From the VBA menu select Insert|Module
• Paste the code below in the edit window
• Back in Excel, put this formula in D5 and copy it down column D

=EVAL(C5)

Function EVAL(rng As Range)
    EVAL = Evaluate(rng.Value)
End Function

 

[xenou]

Do it this way:

In A1 put 600
in B1 put 600
In C1 put 12
In D1 put:
=A1*B1*C1

Then you will have the correct answer. This is the way Excel works best You can use the Text-To-Columns feature to break the elements into individual cells.

 

[Ruddles]

If you're absolutely desperate for a one-formula/no-VBA solution, try this:-

=LEFT(C5,FIND("*",C5)-1)*LEFT(MID(C5,FIND("*",C5)+1,99),FIND("*",MID(C5,FIND("*",C5)+1,99))-1)*MID(MID(C5,FIND("*",C5)+1,99),FIND("*",MID(C5,FIND("*",C5)+1,99))+1,99)

 

[pgc01]

Another option (not tested).

In Sheet1:

- select D5
- create the name Eval, referring to: =EVALUATE(Sheet1!C5)
- enter in D5: =Eval

Copy down.

This named formula will always evaluate the cell to the left, if you use it in K3, it evaluates J3. To be used in Sheet1.

 

[xenou]

Interesting. Why does Evaluate() work in a name but not in a cell (e.g., if you put =Evaluate(C5) in D5 it fails)?

 

[Andrew Poulsom]

Interesting. Why does Evaluate() work in a name but not in a cell (e.g., if you put =Evaluate(C5) in D5 it fails)?

It's an XLM Macro function not a worksheet function.

 

[xenou]

Thanks Andrew. This post is now using two things I've always avoided - relative references in Names, and XLM macros! It's interesting to see some smart use of them here.
ξ

 

[leezhihong]

Another option (not tested).

In Sheet1:

- select D5
- create the name Eval, referring to: =EVALUATE(Sheet1!C5)
- enter in D5: =Eval

Copy down.

This named formula will always evaluate the cell to the left, if you use it in K3, it evaluates J3. To be used in Sheet1.

Thanks! It is perfect!