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).

  • 1
    Does this answer your question? How to SUM a row of numbers after extracting numbers with REGEX? – Tedinoz Apr 2 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 May 1 at 23:41
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 | |
  • This is a great answer. Thank you! – OMA May 3 at 14:24

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.