Hi, is there a way to extract and add together numbers from a cell using regex? For example, i have a text like this:
8x20d 6х10d 8х20f 45х15d 33x10y 25x20y
and i need to have a sum of first numers in one cell like this:
A1 | | | | | | | | | | B1 | | | | | | | C1
41 (8 + 8 + 25) 39 (6 + 33) 45
So i need to find any numbers infront of “x20d” and add them up in one cell.
So far i haven’t had any success, any help is much appreciated!
why exactly 8+8+25 in one group, 6+33 in the other, and 45 in the third ??
why do you mix up x (latin x) and х (kyrillic small letter ha) in the Text ??
8x20d 6х10d 8х20f 45х15d 33x10y 25x20y
Assuming there are 6 groups:
=SUM(IFERROR(REGEX(A2 ; "(\d+)(?=[xх]\d+[dfy] ?)" ;; {1;2;3;4;5;6})*1 ; 0))
returns 125
Calculating with text strings (the ...*1 part) may or may not work, according to the detailed calculation settings. If it doesn’t and results in #VALUE! the IFERROR() even suppresses that. A safer approach is using NUMBERVALUE() (here for integer values specifying separators isn’t needed):
=SUM(IFERROR(NUMBERVALUE(REGEX(A2 ; "(\d+)(?=[xх]\d+[dfy] ?)" ;; {1;2;3;4;5;6})) ; 0))
Also, the formula needs to be entered as array formula, closing it with Shift+Ctrl+Enter instead of just Enter.
Sorry about the mixup with the letters, that’s the text i need to work with. But it can vary, there can be more or less groups than 6. And i need different sums in different cells. In one cell in need the sum of numbers that are infornt of x20d, in other i need the sum of numbers infront of 10d and so on. So from the text in this example i need 3 different formulas in 3 different cells that will return 41, 39 and 45. I can do it with your formula but it wont work if theres more than 6 groups. Thanks very much for your answer! Also your formula works for me and erAck’s doesn’t, it returns only the first number, 8.
And i probably need to mention my version of Calc, sorry about that too, it’s 7.4.7.2
Likely because you didn’t enter it as array formula closing it with Shift+Ctrl+Enter.
If you need the 3 separate results then modify to
=SUM(IFERROR(NUMBERVALUE(REGEX(A2 ; "(\d+)(?=[xх]\d+[dfy] ?)" ;; {1;3;6})) ; 0))
=SUM(IFERROR(NUMBERVALUE(REGEX(A2 ; "(\d+)(?=[xх]\d+[dfy] ?)" ;; {2;5})) ; 0))
=SUM(IFERROR(NUMBERVALUE(REGEX(A2 ; "(\d+)(?=[xх]\d+[dfy] ?)" ;; 4)) ; 0))
And yes, for more than 6 groups you’d need to adapt. But that sounds rather like an input data structure problem, or you need to post-process it to get better data that can be easier handled.
I’ve figured it out, tweaking your formulas a bit. Thanks very much for your help!