Google Sheets REGEXEXTRACT Function

This post will guide you how to use Google Sheets REGEXEXTRACT function with syntax and examples.

Description


The Google Sheets REGEXEXTRACT function uses a regular expression to extract the first matching substring.

The REGEXEXTRACT function can be used to extract the first matching substring from a text string according to a given regular expression in google sheets. So it returns the part of the specified string that matches the given pattern in a regular expression.

The REGEXEXTRACT function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax


The syntax of the REGEXEXTRACT function is as below:

=REGEXEXTRACT (text, regular_expression)

Where the REGEXEXTRACT function arguments are:

  • text -This is a required argument. The text string that you want to extract a substring based on regular expression.
  • regular_expression – This is a required argument. The Regular_expression is a regular expression. It can be used to match the part of the text that you want to extract. The regular expression argument should be entered in double-quote characters.

Note: 

  • The REGEXEXTRACT function takes two arguments. It extracts the first matching substrings according to a given regular expression.
  • The REGEXEXTRACT function also can return multiple results with capture groups. A capture group is also a part of a regular expression that can be enclosed in parentheses.
  • The REGEXEXTRACT function only works with text value as its first argument and returns a text result. If you wish to return a numeric value, and you need to use VALUE function in combination with the REGEXEXTRACT function. If the input value is a number, and you can use the TEXT function to convert them.
  • Google Sheets supports RE2 for regular expressions.

Google Sheets REGEXEXTRACT Function Examples


The below examples will show you how to use google sheets REGEXEXTRACT function to extract a substring from a text according to a regular expression.

#1 Extract the first 5 characters from a string using REGEXEXTRACT function with a regular expression “…..” , type:

=REGEXEXTRACT(B1,".....") //it returns “googl”

google sheets REGEXEXTRACT function

#2 Extract the last 5 characters from a string using REGEXEXTRACT function with a regular expression “…..$” , type:

=REGEXEXTRACT(B1,".....$") // it returns “heets”

google sheets REGEXEXTRACT function

#3 Extract the first whole word from a string using REGEXEXTRACT function with a regular expression “\w+” , type:

=REGEXEXTRACT(B1,"\w+") // it returns “google”

google sheets REGEXEXTRACT function

#4 Extract the last whole word from a string using REGEXEXTRACT function with a regular expression “\w+$” , type:

=REGEXEXTRACT(B1,"\w+$") // it returns “sheets”

google sheets REGEXEXTRACT function

#5 Extract the first number from a string using REGEXEXTRACT function with a regular expression “\d+” , type:

=REGEXEXTRACT(B1,"\d+") // it returns “2021”

google sheets REGEXEXTRACT function

#6 Extract the last number from a string using REGEXEXTRACT function with a regular expression “\d+$” , type:

=REGEXEXTRACT(B1,"\d+") // it returns “2020”

google sheets REGEXEXTRACT function

 

 

 

 

 

 

Related Posts

Assigning Points based on Late Time in Google Sheets
assign points based on late time in google sheets1

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed. If you intend to pursue along this guide, you may do so by downloading ...

Google Sheets COUNTBLANK Function
google sheets countblank function1

This post will guide you how to use Google Sheets COUNTBLANK function with syntax and examples. Description The Google Sheets COUNTBLANK function use to count the number of empty cells in a range of cells. You can use the COUNTBLANK ...

Google Sheets DEGREES Function
google sheets degrees function1

This post will guide you how to use Google Sheets DEGREES function with syntax and examples. Description The Google Sheets DEGREES function converts radians into degrees. And it will return a numeric value. For examples, if you pass the PI() function into ...

Google Sheets COSH Function
google sheets cosh function1

This post will guide you how to use Google Sheets COSH function with syntax and examples. Description The Google Sheets COSH function returns the hyperbolic cosine of a given angle. The purpose of this function is used to get hyperbolic ...

Google Sheets COS Function
google sheets cos function1

This post will guide you how to use Google Sheets COS function with syntax and examples. Description The Google Sheets COS function returns the cosine of a given angle. If you want to supply an angle to COS function in ...

Google Sheets COMBINA Function
google sheets combina function1

This post will guide you how to use Google Sheets COMBINA function with syntax and examples. Description The Google Sheets COMBINA function returns the number of combinations for a given number of items and it includes repetitions. You can use ...

Google Sheets COMBIN Function
google sheets combin function1

This post will guide you how to use Google Sheets COMBIN function with syntax and examples. Description The Google Sheets COMBIN function returns the number of combinations for a given number of items. And you can use the COMBIN function ...

Google Sheets CEILING.PRECISE Function
google sheets ceiling.precise function1

This post will guide you how to use Google Sheets CEILING.PRECISE function with syntax and examples. Description The Google Sheets CEILING.PRECISE function returns a given number rounded up to the nearest multiple of a given number of significance or returns ...

Google Sheets CEILING Function
google sheets ceiling function1

This post will guide you how to use Google Sheets CEILING function with syntax and examples. Description The Google Sheets CEILING function returns a given number rounded up to the nearest multiple of a given number of significance. You can ...

Google Sheets ATANH Function
google sheets atanh function1

This post will guide you how to use Google Sheets ATANH function with syntax and examples. Description The Google Sheets ATANH function returns the inverse hyperbolic tangent of a given number. So you can use the ATANH function to calculate ...

Sidebar