formulasgoogle sheets
This has already been addressed in Excel but I was wondering how to do the same in Google Sheets.
So suppose the B cell has this formula:
="frontend (days)
total: "&sum(B2,B10,B14,B20)
and the C cell has a similar formula. I would like the D cell to add the numbers in those two cells.
Ideas?
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"))))
Seems to do the job.
=SUM(MAX(A1,B1)+MAX(A2,B2)+MAX(A3,B3))
This works only for 3 rows.
A general solution would be following,
In a new column (say C) put this formula
=MAX(A1,B1)
Then apply this formula on some other cell.
=SUM(C:C)
Best Answer
try this:
REGEXEXTRACTpart of formula "\d+.?\d+$" does this:\d- match and retun digit from 0 to 9\d+- gives one or more digits.- any symbol\.- dot?- makes previous symbol optional (we use it here to match integers)$- matches end of string|- ORUpdate 2016/09
In some cases you may need to use different regular expressions and formulas. For example, if we have the text
total = $1,734.00and want to get 1734, we need this formula:this formula does the following: