0

as mention on the title, I am stuck to sum all the value inside multiple brackets within single cell in Google Spreadsheet.

Below is the query that must store within single cell: 26b(1), 26a(41), 27b2(17)

Basically, the value that I want is "59" ( sum of number 1, 41 and 17 ).

Note: if the query above is impossible to find, how about storing the value after equal such as 26b=1, 26a=41, 27b2=17

Improve this question

1 Answer 1

Reset to default
1

Formula

=ArrayFormula(SUM(VALUE(REGEXEXTRACT(SPLIT(A1,","),"\((\d{1,})\)"))))

Brief explanation

We could use regular expressions on Google Sheets but it's support is limited. Fortunately Google Sheets has other features that could help to overcome those limitations.

The SPLIT function splits the cell value on A1 using a comma as separator. Using ArrayFormula makes REGEXTRACT able to take the SPLIT resulting array as argument and to apply the regular expression to each array member. The result is an array of texts, so VALUE is used to convert them to numbers and finally the SUM function sums those numbers.

From REGEXEXTRACT

Notes

  • Google products use RE2 for regular expressions. Google Sheets supports RE2 except Unicode character class matching. Learn more on how to use RE2 expressions.
  • This function only works with text (not numbers) as input and returns text as output. If a number is desired as the output, try using the VALUE function in conjunction with this function. If numbers are used as input, convert them to text using the TEXT function.
Improve this answer
4
  • Hi Ruben, the formula works like a charm when the value is integer. However, when it comes to double ( such as 5.5 ), it breaks.
    – D.D
    Jun 6, 2018 at 1:41
  • @D.D Consider to post a follow up question as the examples provided on the Q only included integers. It will be nice if you do some research about regular expressions. Tip checkout regex101.com
    – Rubén ♦
    Jun 6, 2018 at 1:47
  • It was my mistake to put the example with integer numbers only while forgetting about decimal numbers. I have spend almost a week to figure out this formula and Thanks to you, I was able to sum the integer value. Now, I am only left with decimal points. Cheers
    – D.D
    Jun 6, 2018 at 2:08
  • @D.D I added notes from the REGEXTRACT documentation. They include links about regular expressions.
    – Rubén ♦
    Jun 6, 2018 at 13:58

Your Answer

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

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