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.
Best Answer
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'sRegExp
function):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: