shadowsong
New Member
- Joined
- Mar 15, 2006
- Messages
- 26
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?
Is the math operator in each cell always going to be a plus sign? If not, what else is possible for the math expression?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?
Is the math operator in each cell always going to be a plus sign? If not, what else is possible for the math expression?
Give this formula a try...Yes, it will always be a plus sign. (Although sometimes it might be more than one plus sign, a la "1+1+2".)
Yes, it will always be a plus sign. (Although sometimes it might be more than one plus sign, a la "1+1+2".)
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).
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)))
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...Sure, though I somehow get the impression the OP would have been hoping for a slightly simpler solution!