There are two different formulas in Google Sheets that you can use to extract all numbers from Text and Sum it.

What we want are numbers from text. Even though we want to deal with numbers, without Google Sheets Text functions, we can’t achieve it.

I will explain in detail about the two Google Sheets’ formulas and you can use it on 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.

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 function in a combined form.

=SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789")))

Formula # 2

Here is the Regexreplace and Split Combination.

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

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 the delimiter to split the number.

Formula # 2 Approach:

The second REGEXREPLACE formula replaces all text with “|” and then the Split function uses this “|” as the 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\.]+", "|"), "|"))

Conclusion

The above is one of the most common tasks in Google Sheets. So that learn any of the above formulae that may come very useful for you in the future.

Related:

1. How to Use Regexextract Function in Google Sheets and RE2 Regular Expressions

2. How to Use REGEXMATCH Function in Google Sheets