Evaluating text string as math

[shadowsong]

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?

 

[XOR LX]

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

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.

I'm not saying this is any better (you're correct - it's even actually slightly longer than yours), though I wouldn't say the method employed is that different at all, in essence.

Regards

 

[Rick Rothstein]

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.

I did not actually take your formula apart, rather, I reacted to the structure at the beginning of your formula, namely, this part...

=SUM({-1,1}*MMULT(TRANSPOSE(

 

[mahmed1]

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).

how on earth does that formula work

wow

any chance you can explain this monster formula

 

[markmzz]

Try this too:

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))

Markmzz

 

[XOR LX]

how on earth does that formula work

wow

any chance you can explain this monster formula

Hi mahmed,

How much of it do you already understand? As I said, in essence it's not really any different from the constructions given by Rick and markmzz: the key is that you understand how this standard construction works (I'll use Rick's as as example, as mine required slight refinement):

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

which, given a "+"-delimited string in B1, will parse that string into the required separate elements, e.g. with B1 containing the string "1+2+3+4" will produce the array {"1";"2";"3";"4"}, as required.

Obviously, as Rick and markmzz did, we can then do this for the string in A1, e.g. {"5";"6";"7"}, sum each and then subtract one from the other.

All I did was to use this same construction, but instead of passing it A1 and B1 separately, I passed it an array: the range of two elements A1:B1. All this means is that, with the above examples, instead of returning those two separate single-row arrays, my formula would return both sets of elements in a 2-row-by-4-column array:

{5,1;6,2;7,3;0,4}

(where the zero is necessary to make up the array.)

You can see that the elements in the top and bottom rows correspond precisely with the arrays obtained individually. All that is then necessary is to extract them appropriately, which I did via manipulating with MMULT.

Apologies if this is not the detailed breakdown that you were hoping for, but to do so properly would probably take up three or four pages!

Regards

 

[markmzz]

Try this too (a modification in XOR LX's 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))

Markmzz

 

[XOR LX]

Great stuff, Markmzz.

 

[markmzz]

Great stuff, Markmzz.

Thanks, but the main logic is yours. I only noticed a way to compress the formula.

Markmzz

 

[XOR LX]

Thanks, but the main logic is yours. I only noticed a way to compress the formula.

Markmzz

Perhaps, but 238 characters to 149 is some compression!

Cheers

 

[Rick Rothstein]

Perhaps, but 238 characters to 149 is some compression!

Cheers

+1