Google Sheets – Extract and Sum Numbers from a Cell Containing Text

google sheets

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

Best Answer

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.

Related Topic