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

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.

Now you can use functions like `MAP` and `REDUCE`, which combined with `LAMBDA` allows for very powerful formulas.

`ARRAYFORMULA` cannot be exported, so my method has the advantage of being able to 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.

• Wait I'm confused... The advantage over `ARRAYFORMULA` is that they cannot be exported...? Is the ability to export a disadvantage? Feb 18 at 10:47
• @Matias The wording is a bit confusing, I agree. What I meant is that using ARRAYFORMULA prevents the spreadsheet from being exported, so the method I propose has the advantage that they can be exported. I'll rewrite that part so it's easier to understand. Thanks for pointing it out! Feb 29 at 21:51
• Got it. Thank you for the clarification (: Mar 4 at 21:44