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

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
``````

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.

• 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

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 `SPLIT`function 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 '19 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