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

Google Sheets YEARFRAC Function
google sheets yearfrac function1

This post will guide you how to use Google Sheets YEARFRAC function with syntax and examples. Description The Google Sheets YEARFRAC function returns the number of years between two dates including fractional years. And you can use this function to ...

Google Sheets YEAR Function
google sheets year function1

This post will guide you how to use Google Sheets YEAR function with syntax and examples. Description The Google Sheets YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from ...

Google Sheets WORKDAY.INTL Function
google sheets workday.intl function1

This post will guide you how to use Google Sheets WORKDAY.INTL function with syntax and examples. Description The Google Sheets WORKDAY.INTL function returns the serial number of the date before or after a specified number of workdays. And this function ...

Google Sheets WORKDAY Function
google sheets workday function1

This post will guide you how to use Google Sheets WORKDAY function with syntax and examples. Description The Google Sheets WORKDAY function returns a date or a serial number that represents a date that is the indicated number of working ...

Google Sheets WEEKNUM Function
google sheet weeknum function1

This post will guide you how to use Google Sheets WEEKNUM function with syntax and examples. Description The Google Sheets WEEKNUM function returns the week number of a specific date, and the returned value is ranging from 1 to 53. ...

Google Sheets WEEKDAY Function
google sheet weekday function1

This post will guide you how to use Google Sheets WEEKDAY function with syntax and examples. Description The Google Sheets WEEKDAY function returns an integer value representing the day of the week for a given date and the value is ...

Google Sheets TIMEVALUE Function
google sheets timevalue function1

This post will guide you how to use Google Sheets TIMEVALUE function with syntax and examples. Description The Google Sheets TIMEVALUE returns the decimal number of the time represented by a text string. It will convert a time represented by ...

Google Sheets TODAY Function
google sheets today function

This post will guide you how to use Google Sheets TODAY function with syntax and examples. Description The Google Sheets TODAY function get the current date as a date value. And it will be updated each time when your worksheet ...

Google Sheets TIME Function
google sheets time function1

This post will guide you how to use Google Sheets TIME function with syntax and examples. Description The Google Sheets TIME function converts an hour, minute and second into a time. The TIME function can be used to create a ...

Google Sheets SECOND Function
google sheets second function1

This post will guide you how to use Google Sheets SECOND function with syntax and examples. Description The Google Sheets SECOND function returns the seconds of a time value. Or returns an integer value that represent the second component of ...

Sidebar