How to sum the numbers in cells if there is text?

Question

Guys please help with such a problem. Need to sum numeric cells with text inside? He gave an example of the table, like 1 + 3 + 8 = 12. Is there any way to solve this? Thank !

Answer 1

^{(i assume the task is to sum the numerical components of the cell content, ignoring everything else)}

In LibreOffice Calc, you can do this using a combination of three built-in functions:

• REGEX() to eliminate non-numeric content;
• VALUE() to get the numerical value of the REGEX() result; and
• SUMPRODUCT() to sum up the calculated numerical values in one step.

^{(I've set the "g" (global) flag as 4th argument of the REGEX() function. In your example, it isn't required, but the calculation would fail with more than one non-numerical character)}

1. Use REGEX to eliminate non-numerical stuff:

   

   ^{A2: Input value (string); B2: calculated numerical value; C2: Formula used to calculate B2}

2. Use SUMPRODUCT() and VALUE() to sum the remaining numerical values:

   

   ^{A2:C2: Input values (strings or numbers); D2: calculated numerical value; E2: Formula used to calculate D2}

Here's a detailed overview over the formula: