# Is it possible to use a loop in Google Spreadsheets?

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[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) |
+---+-----------+----------+----------+-----------------+
``````
• 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? Dec 24, 2010 at 23:39
• @Senseful I updated my answer :D test Dec 25, 2010 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. Dec 25, 2010 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.

# Updated 2023 Answer

Now you can use functions like `MAP` and `REDUCE`, which combined with `LAMBDA` allows for very powerful formulas. An advantage they have over `ARRAYFORMULA` is that spreadsheets using it cannot be exported.

An example formula for what you're trying to do would be:

``````= SUM(
MAP(A1:A3, LAMBDA(val,
VALUE(REGEXEXTRACT(val, "\d+"))
))
)
``````

I'm using vertical spacing and indentation to make it easier to read, but you can write it in one line if you wanted.

I also wrapped the `REGEXEXTRACT` with the `VALUE(...)` so the string containing a number is transformed into a number.

You also mention having a new column with the numbers on its own, but you want to avoid it. I highly recommend having separate data in separate columns. The value `foo (123)` is a great example of separate data in the same column, so I'd recommend to split it in two if possible. It'll make working with any of that information much simpler.