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 !
4 Answers
(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)
Use REGEX to eliminate non-numerical stuff:
A2: Input value (string); B2: calculated numerical value; C2: Formula used to calculate B2
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:
The way you show it, it can't be done. There's no arithmetic which will add numbers to text and get a number as a result.
If the h and the t stand for something, maybe you could use column headings to explain?
Another possibility might be to concatenate things somehow, but I don't think that's what you mean.
(Am using Libreoffice.)
If we are talking integers, it seems like you could simply use:
=SUMPRODUCT(-(REGEX(A1:C1;"\d+";;"g")))
And maybe to include negative integers:
=SUMPRODUCT(-(REGEX(A1:C1;"-?\d+";;"g")))
Try this in EXCEL
Separator ;
=VALUE(SUBSTITUTE(A2; {"h";" "}; ""))+VALUE(SUBSTITUTE(B2; {"t";" "}; ""))+C2
Separator ,
=VALUE(SUBSTITUTE(A2, {"h"," "}, ""))+VALUE(SUBSTITUTE(B2, {"t"," "}, ""))+C2
Related