Google Sheets – Convert Text to Number Ignoring Text

google docsgoogle sheetsworksheet-function

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

Best Answer

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

Related Topic