Tuesday, June 28, 2011

yogi_Parse Out Multiple Integer Values From A String In Multiple Cells And Add Them

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

sprkInh2o said:
Parse out multiple integer values from a strings in multiple cells, then add them together?
I have a spreadsheet, which contains the following
        A                    B         C
2 abc(100) abcd(-200)30    abc(200) 
I want to be able to parse out the integer values (within the parenthesis) from A2:B2, sum it up, then place it in C2 (note how it also takes negative values). In the example above, the result should be
          A                  B        C
2 abc(100) abcd(-200)30    abc(200)  100
So far I tried to use "=REGEXEXTRACT(A2, "\d+")" in C2 to extract number from the cells but
1. it only returns the first integer number - 100
2. it does not work for multiple cells