Removing specific text from a box?

Question

Is there a way to remove text from a box in google sheets? Here is a example:

Total: [=sum(stuff here)]
User 1   5 points
User 2   8 points

The [5 points] is one cell. If I just try to sum it up, it just gives me a 0. Is there a way for formula to ignore text and only read numbers in a cell? If you could help, That would be great! :)

Answer 1

The following formula do the job

=ArrayFormula(SUM(VALUE(REGEXEXTRACT(E2:E3,"\d{1,}"))))

• REGEXTRACT use regular expressions to extract text and works fine together with VALUE and ARRAYFORMULA
• \d{1,} is a regular expression that match any digit (0-9) following by any number of digits.

Answer 2

Please use the following formula

="Total: "&ArrayFormula(SUM(IFERROR(REGEXEXTRACT(B2:B,"\d{1,}")*1)))

Functions used:

• ArrayFormula
• IFERROR
• SUM
• REGEXEXTRACT