# 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?

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.

• I went with your regexextract method. It did the trick. Thank you very much! Jan 1, 2018 at 3:39
• Your regexextract solution worked like magic, thank you very much. 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

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.