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:


3 Answers 3


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:


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 :)
    – user6757
    Aug 3, 2014 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.

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

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, 2015 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.