4

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

Ideas?

3 Answers 3

2

try this:

=REGEXEXTRACT(B1,"\d+\.?\d+$|\d+")*1+REGEXEXTRACT(C1,"\d+\.?\d+$|\d+")*1

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:

=REGEXREPLACE(F1,"[^\d]","")/100

this formula does the following:

  • replace all symbols except digits
  • divide by 100 because we get number 173400 in first operation
8
  • Error Function REGEXEXTRACT parameter 2 value "\d+.\d+$" does not match text of Function REGEXEXTRACT parameter 1 value "frontend (days) total: 6".
    – abbood
    Commented May 20, 2016 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. Commented May 20, 2016 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
    Commented Jun 22, 2016 at 13:58
  • hey @MaxMakhrov your code doesn't match a number like this $1,734.00 please update
    – abbood
    Commented Sep 5, 2016 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 Commented Sep 9, 2016 at 16:01
0

I think this should meet your requirement:

=sum(B2,B10,B14,B20,C2,C10,C14,C20)  

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:

##.#
-1

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

3
  • the whole point of the question is to avoid this redundancy
    – abbood
    Commented May 20, 2016 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. Commented May 22, 2016 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
    Commented Apr 6, 2020 at 12:59

Your Answer

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

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