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.

WA92983 Q example



try this:


REGEXEXTRACT part 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
  • | - OR

Update 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.00 and want to get 1734, we need this formula:


this formula does the following:

  • replace all symbols except digits
  • divide by 100 because we get number 173400 in first operation
  • Error Function REGEXEXTRACT parameter 2 value "\d+.\d+$" does not match text of Function REGEXEXTRACT parameter 1 value "frontend (days) total: 6".
    – abbood
    May 20 '16 at 8:53
  • Also, an explanation of what the formula/regexes actually does would be useful, especially for other readers that are trying to do something similar. For example, see the "Explained" section of this answer, in which the different parts of a formula is detailed. May 20 '16 at 9:17
  • btw @MaxMakhrov your code breaks if the the numbers you are summing (ie the contents of B1/C1 etc) are less than 2 digits long.. can you please fix it?
    – abbood
    Jun 22 '16 at 13:58
  • hey @MaxMakhrov your code doesn't match a number like this $1,734.00 please update
    – abbood
    Sep 5 '16 at 9:32
  • 1
    @MaxMakhrov: I think that you will find helpful the following Q&A from Meta Stack Exchange -> meta.stackexchange.com/q/127639/289691
    – Rubén
    Sep 9 '16 at 16:01

I think this should meet your requirement:


provided the cell it is in is formatted something like:

"total: "##.#

However you have accepted an A that does not display total: and that could be achieved with formatting instead such as:


D = sum(B2,B10,B14,B20) + sum(c2,c10,c14,c20)

  • the whole point of the question is to avoid this redundancy
    – abbood
    May 20 '16 at 10:50
  • 1
    So you implement something with considerably more complexity, so avoid a little redundancy? It's an interesting trade off, but I know which option I see as easier to create and maintain. May 22 '16 at 14:21
  • @MaryC.fromNZ You should not judge the complexity of a script against manual work based on the minimal example used in the question. Minimal examples should only illustrate the principle, not the scale of the problem. Maybe the author (or somebody else here) needs to process thousands of cells with different formulas, which can be processed with a single regular expression..
    – Milan
    Apr 6 '20 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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