Google-sheets – Formula that will take a number in a cell, and then add that number to itself -1, until itself -1 = 0

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

Google Spreadsheet actually has regex methods. There are 3 methods:

REGEXEXTRACT(text, regular_expression) - Extracts matching substrings according to a regular expression. Example: REGEXEXTRACT("Needle in a haystack", ".e{2}dle")

REGEXMATCH(text, regular_expression) - Whether a piece of text matches a regular expression. Example: REGEXMATCH("Spreadsheets", "S.r")

REGEXREPLACE(text, regular_expression, replacement) - Replaces part of a text string with a different text string using regular expressions. Example: REGEXREPLACE("Spreadsheets", "S.*d", "Bed")

However, because I don't really know how to use regex, I'll show you how I would do it using SPLIT. This will work even if the numbers aren't separated by a specific character (i.e. space) as shown in the example:

=SUM(SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789"))))

Seems to do the job.