Google sheets - Extract and SUM number in text function doesnt work if there is " . " next-CodePudding

I have this function

=SUM(SPLIT(REGEXREPLACE(A2; "[^\d\.] "; "|"); "|"))

and in A2 i have

The shirt costs 20 euros and the jeans costs 30

So what the function does is to sum the 2 numbers contained in the above text. So it returns 50.

But the problem is that, if in A2 i have the same text but with a dot . at the end:

The shirt costs 20 euros and the jeans costs 30.

It doesnt sum up the 2 numbers and it only returns 20. I think that it only recognizes 20 as a number and not 30???

SAME thing happens if the dot . is in the start of the number:

The shirt costs 20 euros and the jeans costs .30

It returns 20 again.

So I want the function to return 50 again.

I am a begginer on google sheets so i know that it might be very simple to fix, but my knowledge is still very restricted.

Can someone edit my function and paste it in the answers? Thanks.

CodePudding user response：

if this does not work for you:

try:

=SUM(SPLIT(REGEXREPLACE(SUBSTITUTE(A2, ".", ","), "[^\d\.] ", "|"), "|"))