Extract All Numbers from Text and SUM It in Google Sheets

0
456
extract all numbers from text and Sum it

There are two different formulas in Google Sheets that you can use to extract all numbers from text and Sum it. Even though we want to deal with numbers, without Text functions, we can’t achieve it. I will explain in detail about the two Google Sheet formulas and you can use it in your sheet as your custom formula to extract all numbers. No first, second or third set of numbers. These formulas can extract all sets of numbers from a text string irrespective of its position.

First we will see how to extract all numbers from Text in Google Sheets. Then we can go to how to sum it as extracting is the main thing. Sum, that anybody can do.

Extract All Numbers from Text and SUM it

Different Formulas to Extract All Numbers from Text in Google Sheets

Cell Value in A1:

I have paid $100.25 for my blog maintenance last month and this month it’s $60.50 as forecasted.

Formula # 1 to extract all numbers from the above text. Here we are using Google Sheets Split and Concatenate combination.

=SPLIT(A1,CONCATENATE(SPLIT(A1,”.0123456789″)))

Formula # 2

Here it’s Regexreplace and Split Combination.

=SPLIT(REGEXREPLACE(A1, “[^\d\.]+”, “|”), “|”)

Regexreplace and split formulas to extract numbers from text

Both the functions apply different logic in extracting all numbers from text. What are they?

Note: You can skip this part as it’s not important.

Formula # 1 Approach:

The first formula splits all texts and then join it. See there are two SPLIT functions in the formula. The second Split function then use this joined text as delimiter to split the number.

Formula # 2 Approach:

The second REGEXREPLACE formula replaces all text with “|” and then the Split function uses this “|” as delimiter to split the number in cell A1.

Our topic is how to extract all numbers from Text and SUM it in Google Sheets. In this the extracting all numbers from text part, we have done successfully. Now let us see how to sum the numbers extracted from the text.

How to Extract and SUM all Numbers

Just place a SUM in front of the formula as below.

Formula # 1

=SUM(SPLIT(A1,CONCATENATE(SPLIT(A1,”.0123456789″))))

Formula # 2

=SUM(SPLIT(REGEXREPLACE(A1, “[^\d\.]+”, “|”), “|”))

That’s all about it.

LEAVE A REPLY

Please enter your comment!
Please enter your name here