5

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?

6

I would use regexextract like

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

Here, the regular expression grabs the first group of characters that consists of digits 0-9 possibly with the dot. 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. And they they get added.

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.

  • 1
    I went with your regexextract method. It did the trick. Thank you very much! – Quells T Jan 1 '18 at 3:39
  • Your regexextract solution worked like magic, thank you very much. – Liran H Dec 4 '18 at 22:41
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.