0

I have a Google Spreadsheet document with a column which contains string data which includes both numbers and text. For example:

$39,90 + Tax
$69,90 + Tax
$20 / month

I want to sum all those values, but using SUM() doesn't work because of the text included in each field. I've tried to create a new column and come up with a formula to convert those fields into:

39.90
69.90
20.00

But whatever I try, I always get #VALUE! or #ERROR!. So, how do I do this? (either directly sum all numbers contained in the original strings, or create a new column with only the numbers from the other column).

Improve this question
2
  • 1
    Does this answer your question? How to SUM a row of numbers after extracting numbers with REGEX?
    – Tedinoz
    Commented Apr 2, 2020 at 15:04
  • Thanks for the link, but that doesn't work for me, because I don't have a fixed string to depend on, like the slashes in the question posted in that link (as you can see, I can have different strings after the numbers, like a plus sign, a slash or others...). Is there a regex to get a number with a . or a , in the middle?
    – OMA
    Commented May 1, 2020 at 23:41

1 Answer 1

Reset to default
1

Try: =arrayformula(VALUE(substitute(regexextract(A1:A3,"[\d\,]+"),",",".")))

This is essentially the answer offered by user135384 in How to SUM a row of numbers after extracting numbers with REGEX?. There are a few modifications:

  • that topic was concerned with summing a range, so sum() was removed.
  • the output is a string, so the opportunity is taken to substitute the "decimal comma" with a "decimal point".
  • convert the string to a value.

enter image description here

Improve this answer
1
  • This is a great answer. Thank you!
    – OMA
    Commented May 3, 2020 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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