Here are two different formula approaches that you can follow to extract all numbers from text and sum them in Google Sheets.

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

I will explain in detail the two Google Sheets formulas you can use in your sheet as custom formulas to extract all numbers.

These formulas can extract all sets of numbers from a text irrespective of their position.

Extract All Numbers from Text Using SPLIT and CONCATENATE Functions

For the example, we will consider the following text string in cell A1:

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

To extract the numbers 100.25 and 60.50 separately in two cells, you can use the formula below:

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

Explanation:

Syntax: SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

In the inner SPLIT function, which is SPLIT(A1, ".0123456789"):

• text: A1
• delimiter: .0123456789

It returns all the text except the numbers in three parts as follows:

The SPLIT function divides the text string around each character specified in the delimiter as the default split_by_each is TRUE.

We combined this split text and used it as the delimiter in the outer SPLIT. The output will be the numbers in two cells.

This is the first approach to extracting all numbers from a text in Google Sheets. We will see how to sum up these extracted numbers later on.

Extract All Numbers from Text Using SPLIT and REGEXREPLACE Functions

This approach is different. Here, we will use the REGEXREPLACE function to substitute text in the string with | and use that as the delimiter in the SPLIT function.

Here is the formula:

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

The REGEXREPLACE function matches one or more characters that are neither digits nor periods ([^\d.]+) and replaces them with |.

The result will be |100.25|60.50|. The SPLIT function then splits the string at each |.

Summing Extracted Numbers

I am sure all of you can do it now. Simply wrap the above formulas with the SUM function.

Here they are:

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

Both of these formulas will return 160.75, which is the sum of the extracted numbers.

Follow these approaches to extract multiple sets of numbers from text and sum them in Google Sheets.

Common Errors You May Encounter

I haven’t included error handling above as it might complicate the formulas. Let me address those now since you have a clear understanding of the formulas.

The formulas may return a #VALUE error when the specified cell is empty. In that case, you can use the IFERROR function to remove those errors.

Another issue you may face is when the cell contains only numbers. Here, you may need to use an IF logical test. The following two formulas handle those errors:

=LET(ref, A1, IF(ISNUMBER(ref), ref, IFERROR(SUM(SPLIT(ref&"x", CONCATENATE(SPLIT(ref&"x", ".0123456789")))), 0)))
=LET(ref, A1, IF(ISNUMBER(ref), ref, IFERROR(SUM(SPLIT(REGEXREPLACE(ref, "[^\d.]+", "|"), "|")), 0)))

If the specified cell contains text without numbers, both formulas will return 0.

This way, you can extract numbers from texts, sum them, and handle any errors that may occur.

Resources

Here are some related resources.

• Add Custom Text to Numbers in Google Sheets (with Calculation Support)
• Sum Cells With Numbers and Text in a Column in Google Sheets
• How to Split Number from Text When No Delimiter Present in Google Sheets
• Using SUMIF in a Text and Number Column in Google Sheets
• How to Extract Negative Numbers from Text Strings in Google Sheets