# An excel formula to use the string representation of a formula

on
If on cell A1, I have "*1.4", and on cell B1, I have 5, is there a formula I can put in cell C1 that says "take the value in B1 and apply the "formula" I put in cell A1, to get he result of "5*1.4"? As a worksheet formula, not a UDF please....

Looking for something prettier than using nested IF's, which I know how to do... and looks like this...

=IF(LEFT(A1,1)="*",B1*RIGHT(A1,LEN(A1)-1),IF(LEFT(A1,1)="/",B1/RIGHT(A1,LEN(A1)-1),B1+A1))
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor, Independant Technology Professional

Commented:
Do you mean besides a formula like C1: =B1&A1?

Commented:
yeah, not that, I'm looking for the mathematical result...
EE Topic Advisor, Independant Technology Professional

Commented:
So do you want the result to be "5*1.4", or "7"?

Commented:
I'd like C1 to evaluate 5*1.4 and give me 7
Analyst Assistant

Commented:
Not much prettier, in fact probably uglier, but at least it gets rid of all the nested IFs.

=CHOOSE(MATCH(LEFT(A1), {"*","/","+","-"},0), B1*MID(A1,2,LEN(A1)),  B1/MID(A1,2,LEN(A1)), B1+MID(A1,2,LEN(A1)), B1-MID(A1,2,LEN(A1)))
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
The Excel 4 macro language included the EVALUATE function that does what you want--and it can still be used in the very most recent version of Excel. The trick is that you have to create a named formula for the purpose.

Let's suppose that you put *5 in cell A2 and 4 in cell B2. You would then select cell C2 and use the Formulas...Define Name menu item to create a named formula called EvaluateLeft2 with a Refers to of =EVALUATE(B2 & A2)

Now in cell C2 you put the formula =EvaluateLeft2 and Excel will return 20. If you copy this formula, you may apply it anywhere in your workbook and if will always concatenate the cell at the immediate left to the cell two to the left--and evaluate the result of that concatenation.

The trick works as long as the concatenated result of the two adjacent cells may be evaluated to return a numerical result.

Commented:
THANKS! I knew something like this must exist.

Is the named formula way how you access all deprecated functions?

Also, this works for multiplication and division and even subtraction, but not addition (it gives "54", 5+4). Any idea how to get around that?
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Prefix +4 with a single quote (e.g. '+4) so Excel knows you are entering text rather than a formula ion column A.

Commented:
Perfect, thanks so much!

Do more with