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?
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...
I did not actually take your formula apart, rather, I reacted to the structure at the beginning of your formula, namely, this part...Perhaps different, but "completely"? I used the precise same construction - TRIM(MID(SUBSTITUTE(...etc., - as you, though rather than repeating this for each of A1 and B1, I simply performed the operation on the range A1:B1, and then manipulated the resulting array.
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).
Use Ctrl+Shift+Enter to enter the formula
=SUM(IFERROR(--MID(SUBSTITUTE(B1,"+",REPT(" ",LEN(B1))),(ROW(INDIRECT("1:"&LEN(B1)))-1)*LEN(B1)+1,LEN(B1)),0),
IFERROR(-MID(SUBSTITUTE(A1,"+",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)))-1)*LEN(A1)+1,LEN(A1)),0))
how on earth does that formula work
wow
any chance you can explain this monster formula
Use Ctrl+Shift+Enter to enter the formula
=SUM(IFERROR({-1,1}*MID(SUBSTITUTE(A1:B1,"+",REPT(" ",MAX(LEN(A1:B1)))),(ROW(INDIRECT("1:"&MAX(LEN(A1:B1))))-1)*MAX(LEN(A1:B1))+1,MAX(LEN(A1:B1))),0))
Great stuff, Markmzz.
Perhaps, but 238 characters to 149 is some compression!
Cheers