How to Extract Negative Numbers from Text in Google Sheets

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.

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.

extract negative numbers google sheets

 

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.

extract multiple negative numbers google sheets

 

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:

  1. First, select the cell that will contain our REGEXEXTRACT formula. In this example, we’ll start with cell A2.
    select cell to place formula for extracting
  2. 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.
    add REGEXEXTRACT formula
  3. Use the Fill Handle tool to drag the formula down and complete the rest of the column.
    use Fill Handle tool to extract from multiple rows

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

  1. 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.
    extract multiple negative numbers google sheets using REGEXREPLACE
  2. Use the Fill Handle tool to extract the negative numbers from the rest of the text strings in your dataset.
    extract more than 1 negative number from a string in google sheets

 

 

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. 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like