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.

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.length; c++) {
var pattern = new RegExp("\\d+", "gi");
sum += parseFloat(pattern.exec(v[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) |
+---+-----------+----------+----------+-----------------+
``````
• 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

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.