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

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?

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.

• 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

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.