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
Just saved me lots of time! You are the BEST Prashanth
BIG BIG THANKS!!!
Hello there,
Your formula is amazing, but what if the numbers are negative?
I tested it both formulas and only sum positive. ex. (Car 20, light 10, Amazon -10) result 40 instead of 20.
Thank you very much for your help.
Hi, Alejandro Rodriguez,
You can use this REGEXREPLACE formula.
=sum(SPLIT(REGEXREPLACE(A1, "[^\d\.-]+", "|"), "|"))