# 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". 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? Jun 22 '16 at 13:58
• hey @MaxMakhrov your code doesn't match a number like this \$1,734.00 please update Sep 5 '16 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
– Rubén
Sep 9 '16 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 May 20 '16 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. 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.. Apr 6 '20 at 12:59