Google-sheets – Removing specific text from a box

formulasgoogle sheets

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! 🙂

Best Answer

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.
Related Topic