7

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?

3 Answers 3

10

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.

3
  • 1
    I went with your regexextract method. It did the trick. Thank you very much!
    – Quells T
    Jan 1, 2018 at 3:39
  • Your regexextract solution worked like magic, thank you very much.
    – Liran H
    Dec 4, 2018 at 22:41
  • @Vincent your suggested edit looks good but I'm not sure that the link that to SO that you are suggesting is apropiate as it's not specific to JavaScript regex. A link to an answer that uses JavaScript regex or a question that explicitely requris JavaScript regex will be better, I think.
    – Rubén
    Dec 18, 2020 at 17:36
0

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.

0

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.

Your Answer

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

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