How to SUM a row of numbers after extracting numbers with REGEX?

Question

I have columns of data that are of the form:

A1: "IDjohn / 35.00 / california"
A2: "IDmike / 25.00 / oregon"
A3: "IDrebecca / 40.00 / ohio"
B1: "IDchang / 20.00 / washington"
B2: "IDwill / 25.00 / delaware"

Each cell is quite dense with info. I'm trying to find a formula that would summarize a whole column after extracting the number between the slashes. So the summation of column A would result in 100.00 and the summation of column B would result in 45.00.

Is such a thing possible? Or do I need to rework the data format into a more parse-able format?

Answer 1

Use regexextract like

=arrayformula(sum(0+iferror(regexextract(A1:A3, "[\d\,\.]+"))))

How this works:

• Regular expression extracts the first group of characters that consists of digits 0-9 commas or dots (such as 12,345.67, or 1.23, or just 1). This is a basic number match, you may need a stricter number regular expression.

• The wrapper iferror is needed in case there is no such group (maybe the cell is empty), because regexextract has the annoying habit of showing #N/A instead of just giving an empty string.

• Adding 0 forces Sheets to treat the result as a number.

• Arrayformula and sum perform the summation over a range after running the formula.

A slightly shorter alternative is regexreplace:

=arrayformula(sum(0+regexreplace(A1:A3, "[^\d\,\.]", "")))

Here, regexreplace removes everything that is not a digit or period. An advantage is that there can be no error thrown. A disadvantage is that there is a greater chance of getting wrong results if, say, the third column has some digits 0-9 in it. Those digits would get appended to the number you want.

Answer 2

I was struggling with this too. I had texts like

A1: 3 Test 1 something
B1: 1.5 Something else here, which does not matter
C1: 3

The above formula almost worked, except when there was only number in the cell, it did not work. A slight modification would be to use TO_TEXT function. So, something like this:

=arrayformula(sum(0+iferror(regexextract(to_text(A1:C1), "[\d\.]+"))))

I don't mean to say @user135384's answer is not correct. Just the number was not part of the question either. But in case you run into this, the above to_text function would help. Thanks @user135384 for your answer.

Answer 3

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.