0

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! :)

  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Nov 12 at 5:07
  • @marikamitos I had a meeting. Sorry! – Eric Pan Nov 12 at 5:25
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.
| improve this answer | |
0

Please use the following formula

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

enter image description here

Functions used:

| improve this answer | |
  • By the way have you tested the accepted formula when empty cells are present? – marikamitsos Nov 12 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.