# 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". – 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. – Vidar S. Ramdal 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
• @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 – abbood 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. – MaryC.fromNZ May 22 '16 at 14:21