Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have the following document:

+---+-----------+
|   | A         |
+---+-----------+
| 1 | Foo (100) |
| 2 | Bar (30)  |
| 3 | Baz (50)  |
+---+-----------+

I'd like to use a REGEXEXTRACT to extract the numbers from each row in column A and then total them. For example:

=SUM(REGEXEXTRACT(A1, "\d+"), REGEXEXTRACT(A2, "\d+"), REGEXEXTRACT(A3, "\d+"))

The problem is that this list will grow and for each row, I will need to make the formula even longer. Ideally I want a way to loop on all items such as:

=SUM_LOOP(A1:A3, REGEXEXTRACT(CELL, "\d+"))

... where the first argument is the range to loop over and CELL is the current cell in the loop.

I know that I can easily do this by creating a column to the right using a formula such as =REGEXEXTRACT(A1, "\d+") and then expanding it downwards, and performing a SUM on this new column, but I would like to avoid creating a new column if possible.

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

If you have row array with your values you can use this custom function to calculate the sum of the numbers (you can't use REGEXEXTRACT in the Script Editor, instead you have to use the JavaScript's RegExp function):

function sumLoop(v) {
  var sum = 0;

  for (var c = 0; c < v[0].length; c++) {
    var pattern = new RegExp("\\d+", "gi");  
    sum += parseFloat(pattern.exec(v[0][c]));
  } 

  return sum;
}​

For some reason if in the above function we'll ask for the number of rows (v.length as they mention in Google Forums), it returns an error.

So in order to see it action do the following, after copying the above function in the Script Editor:

+---+-----------+----------+----------+-----------------+
|   | A         |  B       | C        | D               |
+---+-----------+----------+----------+-----------------+
| 1 | Foo (100) | Bar (30) | Baz (50) | =sumLoop(A1:C1) |
+---+-----------+----------+----------+-----------------+
share|improve this answer
    
Any idea if I can pass the function to run? In this case it would be REGEXEXTRACT(CELL, "\d+") and then run it with something equivalent to an eval function? –  Senseful Dec 24 '10 at 23:39
    
@Senseful I updated my answer :D test –  Lipis Dec 25 '10 at 13:16
    
I guess the problem is that I'm asking two questions in one. This answer answers my first question. I'll ask another question about the eval. –  Senseful Dec 25 '10 at 19:33
add comment

A kind of loop-like behavior can be achieved using array formulas. You can enter following formula:

=SUM(ARRAYFORMULA(VALUE(REGEXEXTRACT(B2:B4;"\d+"))))

in a cell to have there computed the value you need in range you want.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.