Extract and sum numbers from a cell containing text that contains numbers

Question

Suppose cell A1 contains a string that includes substrings that are either integers or decimals, the decimals being of a form that would match the regex \d+\.\d+ . For example, the contents of cell A1 might be as follows:

foo 1; bar: 0.5 grue 23 frozz-bozz

What formula could I use in a different cell that would extract and sum those numbers. I.e. a formula such that, if the contents of A1 were as given above, the result would be:

24.5

Answer 1

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.

Answer 2

A Regex alternative:

=SUM(SPLIT(REGEXREPLACE(A1, "[^\d\.]+", "|"), "|"))

The regex [^\d\.]+ matches everything until a number is found, and replaces the text with a |. Given the input string foo 1; bar: 0.5 grue 23 frozz-bozz, the regex produces |1|0.5|23|.

Then the SPLITfunction splits the resulting string by | into an array, and the SUM sums it all up.

Feel free to take a look at the example spreadsheet I've set up.

Answer 3

There is an unfixed bug in Google Docs spreadsheets, which apparently causes both REGEXREPLACE and SPLIT to yield only partial output from within an ARRAYFORMULA parenthesis. As such, neither of the previously given answers are easily adaptable into formulas that can be passed an array of cells.

Here is a workaround, using Google Apps Script to create a custom function called numextractsum :

function numextractsum(inputStrings) {
  // Function to find ints or floats in text, and sum them
  inputStrings = "" + inputStrings; // Forces input object to string
                                    // and automatically concatenates
                                    // if passed an array by the
                                    // calling spreadsheet.
  var total = 0;
  matches = inputStrings.match(/(\d+\.\d+|\d+)/g);
  for(var j in matches) { total += Number(matches[j]); }
  return total;
}