3

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
2

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

enter image description here

Seems to do the job.

  • Thanks. I tried REGEXEXTRACT before posting my question because its name and Google's summary description of it intuitively suggested to me that it would be the appropriate function. However, it doesn't seem to be terribly useful for my use case, so either my intuition was off or its name and description were poorly chosen. Anyhow, I decided not to mention it in my question, in case people got the idea I was somehow wedded to using it :) – sampablokuper Aug 3 '14 at 20:56
4

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.

  • If some of the cells are empty you can use: IF(ISBLANK(A1),, SUM .... here ) – Dave Jan 19 '15 at 22:18
  • Thanks, this worked for my problem too, but just needed to replace sum with average. – deltaray Sep 6 at 19:00
2

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;
}
  • Please do not use URL shorteners here. – ale Oct 3 '15 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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