Are you struggling to extract numbers from text and sum them in Google Sheets? Look no further! In this article, we will explore two different formulas that will help you achieve this task effortlessly.
Extract All Numbers from Text in Google Sheets
Google Sheets offers powerful text functions that allow you to extract numbers from text. Let’s dive into the two formulas and learn how to use them effectively.
Formula #1: Split and Concatenate
Imagine you have a text string in cell A1 that says, “I have paid $100.25 for my blog maintenance last month and this month it’s $60.50 as forecasted.” To extract all numbers from this text, you can use the following formula:
=SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789")))
This formula uses a combination of the SPLIT and CONCATENATE functions to split the text into separate parts and then concatenate them to extract the numbers.
Formula #2: Regexreplace and Split Combination
Alternatively, you can also utilize the Regexreplace and Split functions to extract numbers from text. Here’s how the formula looks:
=SPLIT(REGEXREPLACE(A1, "[^d.]+", "|"), "|")
This formula replaces all non-digit characters with “|” and then uses the Split function to split the text based on the “|” delimiter.
Both formulas work differently but achieve the same result of extracting all numbers from text. Choose the one that suits your needs best.
Summing the Extracted Numbers
Once you have successfully extracted the numbers from the text, the next step is to sum them. Simply add the SUM function in front of the formula you used for extraction.
Formula #1:
=SUM(SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789"))))
Formula #2:
=SUM(SPLIT(REGEXREPLACE(A1, "[^d.]+", "|"), "|"))
By incorporating the SUM function, you can easily calculate the total sum of the extracted numbers.
Conclusion
Extracting numbers from text and summing them in Google Sheets is a common task, and now you know how to do it effortlessly. Mastering these formulas will prove to be incredibly useful for your future endeavors.
To discover more Google Sheets tips and tricks, visit Crawlan.com.
