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?

2 Answers 2

9

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.

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.