Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
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 at 20:56

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.

share|improve this answer

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;
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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