The REGEXEXTRACT and REGEXREPLACE functions are **useful when you need to extract negative numbers from a given text string in Google Sheets.**

These functions allow users to define regular expressions that tell Google Sheets what to look for in a text string.

##### Table of Contents

Regular expressions are a sequence of characters that specify a pattern in the text. For example, the regular expression /sheet/ tells our program to look for the first instance of the word ‘sheet’.

Regular expressions are incredibly useful if we want to extract negative numbers from a text string. The regular expression ‘/\d/’ helps match numbers, specifically decimal numbers. Since we need to account for negative numbers, we’ll have to use a more complex regular expression such as ‘\-\d*\.?\d+’.

Let’s take a look at a sample use case where we can use regular expressions to extract a negative number.

Suppose you have a dataset of strings that may contain numerical data. You want to flag all strings with negative values. You also want to extract those negative values into a separate column. How can we do this in Google Sheets?

With the `REGEXEXTRACT`

function, we can get the substring corresponding to the first negative number that matches. This function also supports the use of capture groups to return multiple results.

Users can also use the `REGEXREPLACE`

function to perform a similar extraction. We’ll use this function to replace the characters surrounding the negative values with a delimiter such as a pipe character ‘|’. We then use the `SPLIT`

function to return all negative values found in the string.

Now that we know when to use Google Sheets functions to extract negative numbers from text strings, let’s look at a sample spreadsheet!

**A Real Example of Extracting Negative Numbers from Text Strings in Google Sheets**

Let’s take a look at a real example of the `REGEXEXTRACT`

function being used in a Google Sheets spreadsheet to retrieve negative numbers from text strings.

The table below contains two columns. The first column contains a list of strings flagged as having negative numbers. We used a Google Sheet formula in the second column to extract the negative number from the string.

To get the values in Column B, we can use the following formula:

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

Let’s break down the formula into its different parts. First, we use the `REGEXEXTRACT`

function to try to find a substring in cell **A2**. The substring should match the regular expression ‘**\-\d*\.?\d+**’.

If the `REGEXEXTRACT`

formula finds no matches, the `IFNA`

function catches the error and returns an empty string. We wrap the formula with a `VALUE`

function to convert the string of the negative number into a numeric value.

The formula above is limited to returning only a single instance. If you want to return multiple numbers from a string, we can use the `REGEXREPLACE`

method instead.

The example below uses the `REGEXREPLACE`

function to extract multiple numbers from our string.

To get the values in Column B and C, we can use the following formula:

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

You can make your own copy of the spreadsheet above using the link attached below.

If you’re ready to try out these functions in Google Sheets, let’s start writing it ourselves!

**How to Extract Negative Numbers from Text Strings in Google Sheets**

This section will guide you through each step needed to begin using the Google Sheet function to extract negative numbers from text strings. You will learn how to use both `REGEXEXTRACT`

and `REGEXREPLACE`

to search for one or more negative numbers in a given string.

Follow these steps to start using the `REGEXEXTRACT`

function:

- First, select the cell that will contain our
`REGEXEXTRACT`

formula. In this example, we’ll start with cell**A2**.

- Next, enter the
`REGEXEXTRACT`

formula mentioned in the prior section. Make sure that the first argument of the`REGEXEXTRACT`

formula references the text string you want to extract from. Afterward, hit the**Enter key**to return the final result.

- Use the
**Fill Handle**tool to drag the formula down and complete the rest of the column.

If you want to extract multiple negative values from a text string, we can use the `REGEXREPLACE`

method.

- Add the
`REGEXREPLACE`

formula to your desired cell. Hit the**Enter****key**to return an array of negative numbers found within your text string. The formula will automatically return each number in an individual cell.

- Use the
**Fill Handle**tool to extract the negative numbers from the rest of the text strings in your dataset.

This step-by-step guide should be all you need to start extracting negative numbers from text strings in Google Sheets. After reading our article, you should now understand how regular expressions can help with extracting numerical data from strings.

The `REGEXREPLACE`

and `REGEXEXTRACT`

functions are just two examples of text functions you can use in Google Sheets. With so many other Google Sheets functions available, you can surely find the right one for your use case.

Are you interested in learning more about what Google Sheets is capable of? Subscribe to our newsletter to find out about the latest Google Sheets guides and tutorials from us.