An excel formula to use the string representation of a formula

newparadigmz
newparadigmz used Ask the Experts™
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian BEE Topic Advisor, Independant Technology Professional

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

Author

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

Commented:
So do you want the result to be "5*1.4", or "7"?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I'd like C1 to evaluate 5*1.4 and give me 7
NorieAnalyst 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.

Author

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?
byundtMechanical 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.

Author

Commented:
Perfect, thanks so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial