I suggest that the erroneous matching in the original solution can be avoided if the numbers to match have a recognizable structure, like those in the OP. A pattern like "\d+\.\d\d"
will force the recognition of only those numbers with two digits after the decimal.
In addition, where @user135385 uses the pattern "[\d\,\.]+"
to find all numbers, it will erroneously extract negatives as positives. To capture negative values that are entered with either a -
or with parentheses takes a regex pattern like "[-\(]? *[\d,]*\.\d\d\)?"
will match any of the following:
123.45
( 123.45)
- 123.45
(123.45)
-123.45
.45
but it won't match
45
because that is not the desired syntax.
This evening I've been working on very similar problem to the OP and I started down the path of extracting the various fields of the original data into separate columns.
I originally created three regextract functions that differed only in where they had the parentheses to specify what part of the regex to return. I copied them for every row in the original source. Then I discovered that I could put all three pairs of parentheses into a single regex pattern at Google Sheets would expand that into an array with the second and third parts extracted into the next columns. Very useful!
But in my version, the column of numbers summed to zero. I found this thread while searching for a solution to that problem.
Thanks to @user135384's answer, I now know about the trick of adding 0
to a numeric string to force the result to be number (thank you!).
I also learned that I don't have to extract the numbers into a separate column, I can sum that as a virtual column with a single formula, without having to change the source data at all. That's awesome.