# How to sum cells in Google Sheets that contain numbers and text?

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?

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
• Error Function REGEXEXTRACT parameter 2 value "\d+.\d+\$" does not match text of Function REGEXEXTRACT parameter 1 value "frontend (days) total: 6". 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? Commented Jun 22, 2016 at 13:58
• hey @MaxMakhrov your code doesn't match a number like this `\$1,734.00` please update Commented Sep 5, 2016 at 9:32
• @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

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:

``````##.#
``````

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

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