12

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.

3 Answers 3

7

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) |
+---+-----------+----------+----------+-----------------+
0
19

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.

6

Updated 2023 Answer

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.

3
  • Wait I'm confused... The advantage over ARRAYFORMULA is that they cannot be exported...? Is the ability to export a disadvantage?
    – Matias
    Feb 18 at 10:47
  • 1
    @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 (:
    – Matias
    Mar 4 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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