Evaluating text string as math

shadowsong said:

I have two columns of text cells with values such as A1="1+1" and B1="1+2". I want to calculate =B1-A1 and get 1 as a result. Can this be done without using VBA?

Click to expand...

Is the math operator in each cell always going to be a plus sign? If not, what else is possible for the math expression?

Rick Rothstein said:

Is the math operator in each cell always going to be a plus sign? If not, what else is possible for the math expression?

Click to expand...

Yes, it will always be a plus sign. (Although sometimes it might be more than one plus sign, a la "1+1+2".)

shadowsong said:

Yes, it will always be a plus sign. (Although sometimes it might be more than one plus sign, a la "1+1+2".)

Click to expand...

Give this formula a try...

=LEFT(A1,FIND("+",A1)-1)+MID(A1,FIND("+",A1)+1,99)-LEFT(B1,FIND("+",B1)-1)-MID(B1,FIND("+",B1)+1,99)

This array formula** will calculate B1-A1 no matter how many "+" signs there are in each:

=SUM({-1,1}*MMULT(TRANSPOSE(ROW(INDIRECT("1:"&MAX(1+LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,"+",""))))))^0,IFERROR(0+TRIM(MID(SUBSTITUTE(A1:B1,"+",REPT(" ",41)),41*(ROW(INDIRECT("1:"&1+MAX(LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,"+","")))))-1)+1,41)),0)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

shadowsong said:

Yes, it will always be a plus sign. (Although sometimes it might be more than one plus sign, a la "1+1+2".)

Click to expand...

XOR LX said:

This array formula** will calculate B1-A1 no matter how many "+" signs there are in each:

=SUM({-1,1}*MMULT(TRANSPOSE(ROW(INDIRECT("1:"&MAX(1+LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,"+",""))))))^0,IFERROR(0+TRIM(MID(SUBSTITUTE(A1:B1,"+",REPT(" ",41)),41*(ROW(INDIRECT("1:"&1+MAX(LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,"+","")))))-1)+1,41)),0)))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Click to expand...

I missed the "sometimes it might be more than one plus sign" part (I read too quickly)... thanks for picking up on than 'XOR LX'.

Sure, though I somehow get the impression the OP would have been hoping for a slightly simpler solution!

I guess it's a moot point whether using a Defined Name with EVALUATE() is technically "using VBA"?

Regards

XOR LX said:

This array formula** will calculate B1-A1 no matter how many "+" signs there are in each:

=SUM({-1,1}*MMULT(TRANSPOSE(ROW(INDIRECT("1:"&MAX(1+LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,"+",""))))))^0,IFERROR(0+TRIM(MID(SUBSTITUTE(A1:B1,"+",REPT(" ",41)),41*(ROW(INDIRECT("1:"&1+MAX(LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,"+","")))))-1)+1,41)),0)))

Click to expand...

I hate array formulas, but that's because I'm usually dealing with very large arrays in files that are already slow to calculate. It seems to work fine in this instance. Thanks!

You're welcome! (Array formulas are to be cherished, not hated! )

XOR LX said:

Sure, though I somehow get the impression the OP would have been hoping for a slightly simpler solution!

Click to expand...

I just noticed that my solution for the single plus sign was backwards... I subtracted the Column B calculation from the Column A calculation. Here is an array-entered** formula (which subtracts the values correctly) that is slightly shorter than you formula which uses a completely different method of calculating the evaluated value...

=SUM(--TRIM(MID(SUBSTITUTE(B1,"+",REPT(" ",99)),ROW(INDIRECT("1:"&1+LEN(B1)-LEN(SUBSTITUTE(B1,"+",""))))*99-98,99)))-SUM(--TRIM(MID(SUBSTITUTE(A1,"+",REPT(" ",99)),ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))))*99-98,99)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Just as a side note, the above code works in XL2003 (probably earlier versions as well) and above whereas your formula, due to the IFERROR function call, only works in XL2007 and above.