How to extract text values from a cell in google sheets and add them (without Google Apps Script)

[Vector8086]

I have a google sheet which tracks various categories of values, and these categories get summed up. So, the final result is a number.

Sample data:
"E(12), P-6, TW=4, 5" => The key values are: 12+6+4+5, formula should return 27
"2E, 2P, 6TW, 2O" => The key values are: 2+2+6+2, formula should return 12

I'm able to do this with a custom function via GAS, but if there was a way to do it in a formula, using the built-in functions, my google sheet would run much faster. It contains thousands of instances of the custom function.

 

[veyselemre]

Hi, try this..

=SUM(ARRAYFORMULA(VALUE(REGEXEXTRACT("a"&SPLIT(A1,","),"(\d+)+"))))

 

[JEC]

This is what Excel is missing….

 

[Vector8086]

Hi, try this..

=SUM(ARRAYFORMULA(VALUE(REGEXEXTRACT("a"&SPLIT(A1,","),"(\d+)+"))))

That did it. I just added a conditional statement for cases where the cell is blank:

=IF(A1="","",SUM(ARRAYFORMULA(VALUE(REGEXEXTRACT("a"&SPLIT(A1,",")
[/QUOTE]

For extra points, it'd be great if this could be modified to take in a range and return the grand total, but this gets the job done. Thank you!