It’s not tough to construct a regular expression (RE2) to extract negative numbers from different text strings in Google Sheets.

Out of the three related functions, we can use either REGEXEXTRACT or REGEXREPLACE. There’s no scope for the third one, i.e., REGEXMATCH, here.

You may have the following queries in mind. Let me clarify them first.

FAQs About Extracting Negative Numbers in Google Sheets

1. Can we use the extracted negative or positive numbers in other calculations?

Yes, we can. For that, we may need to use the VALUE function to convert the extracted negative numbers (in text format) into number format.

2. What about multiple (positive or negative) numbers in a text string?

We can extract all of them into multiple columns in the corresponding row.

3. Do you provide an array formula to get negative or positive numbers from a string?

Yep! We can code an array formula using the ArrayFormula function and an open/closed range within REGEXREPLACE.

Extract All Numbers Irrespective of Their Sign from Text Strings

I have various sample text strings in the range A2:A16. Let’s first use a non-array formula to reach our goal.

First of all, select A2:A16 and go to Format > Number > Plain text.

In cell B2, insert the following combo formula and drag it down as far as you want to extract all positive or negative numbers:

=VALUE(IFNA(REGEXEXTRACT(A2, "\-?\d*\.?\d+"),))

Since our values are in A2:A16, I have dragged this formula until B16.

There are three functions in use in the above formula, and their purposes are as follows:

• REGEXEXTRACT – Extract numbers irrespective of their sign from the provided text.
• IFNA – Return blank if the above formula returns #N/A!.
• VALUE – Convert the extracted text-formatted numbers into actual numbers.

Formula Explanation (REGEXEXTRACT)

Here’s the explanation of the regular expression \-?\d*\.?\d+:

• \-? – Matches the hyphen character zero or one time.
• \d* – Matches digits (0–9) zero or unlimited times.

In the above two, the quantifiers are the question mark (?) and the asterisk (*).

• \.? – Matches the period (decimal point) zero or one time.
• \d+ – Matches digits (0–9) one or more times.

Array Formula Version

We can convert the above into an array formula.

Closed Range:

=ArrayFormula(VALUE(IFNA(REGEXEXTRACT(A2:A16, "\-?\d*\.?\d+"),)))

Open Range:

=ArrayFormula(IF(A2:A="",,VALUE(IFNA(REGEXEXTRACT(A2:A, "\-?\d*\.?\d+"),))))

How to Get Multiple Negative or Positive Numbers from a Single Text

In the above examples, some of the values in A2:A16 contain multiple numbers.

For example, cell A4 contains the numbers 250 and -500.

The previous formulas only return 250. How do we get both?

Here, we’ll use REGEXREPLACE instead of REGEXEXTRACT.

In cell B2, copy-paste the following combo formula and drag it down:

=SPLIT(REGEXREPLACE(REGEXREPLACE(A2, "[^(\-?\d*\.?\d+)]", "|"),"(\|){1,}","| "),"|")

Formula Explanation (REGEXREPLACE)

Earlier, we used the regular expression \-?\d*\.?\d+ to extract negative or positive numbers in Google Sheets.

Here, we’ve used a negated character class ([^...]) to replace all characters except numbers (positive or negative).

Example:

=REGEXREPLACE(A2, "[^(\-?\d*\.?\d+)]", "|")

This outputs multiple pipes as placeholders. To fix that, we add a second REGEXREPLACE to clean them:

=REGEXREPLACE(REGEXREPLACE(A4, "[^(\-?\d*\.?\d+)]", "|"),"(\|){1,}","| ")

Output for A4: | 250| -500

Finally, SPLIT divides them into separate cells.

Array Formula Version

=ArrayFormula(IFERROR(SPLIT(REGEXREPLACE(REGEXREPLACE(A2:A, "[^(\-?\d*\.?\d+)]", "|"),"(\|){1,}","| "),"|")))

Extracting Negative Numbers Only in Google Sheets

When using the earlier REGEXEXTRACT formulas, simply remove the ? quantifier after the hyphen to extract only negative numbers in Google Sheets.

Example:

=VALUE(IFNA(REGEXEXTRACT(A2, "\-\d*\.?\d+"),))

However, this method may not work well with REGEXREPLACE. In that case, we can apply an IF logical test:

=ArrayFormula(
  IF(
    SPLIT(REGEXREPLACE(REGEXREPLACE(A2, "[^(\-?\d*\.?\d+)]", "|"),"(\|){1,}","| "),"|")<0,
    SPLIT(REGEXREPLACE(REGEXREPLACE(A2, "[^(\-?\d*\.?\d+)]", "|"),"(\|){1,}","| "),"|"),
  )
)

But we can simplify the formula, avoid repeated calculations using LET, and also remove empty result columns:

=ArrayFormula(
  LET(
    val, SPLIT(REGEXREPLACE(REGEXREPLACE(A2, "[^(\-?\d*\.?\d+)]", "|"), "(\|){1,}", "| "), "|"), 
    test, IF(val < 0, val, ),
    IF(SUMPRODUCT(test) = 0, , TOROW(test, 3))
  )
)

This ensures that only negative numbers are returned, while keeping the results clean and efficient.

Common Errors When Extracting Negative Numbers in Google Sheets

• #N/A! Error in REGEXEXTRACT
  This happens when no number is found in the text. Wrap your formula with IFNA to return a blank instead of the error.
• #REF! Error in SPLIT
  Occurs when the formula tries to overwrite existing data. Empty the target cells/array before applying the formula.
• Negative Numbers Returning as Text
  Sometimes extracted numbers remain in text format. Use the VALUE function to convert them into actual numbers for calculations.
• Decimal Numbers Not Extracting Properly
  Make sure your regular expression includes \.? for decimals. Example: \-?\d*\.?\d+.

That’s all. Thanks for reading—enjoy extracting negative numbers in Google Sheets!

Resources

• Add Custom Text to Numbers in Google Sheets (with Calculation Support)
• Extract All Numbers from Text and Sum Them in Google Sheets
• Convert Currency-Formatted Text to Numbers in Google Sheets
• Sum Cells With Numbers and Text in a Column in Google Sheets
• Split Numbers from Text Without Delimiters in Google Sheets
• Using SUMIF in a Text and Number Column in Google Sheets