Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022

Reading Time: 5 minutes

The REGEXEXTRACT function is used to extract substrings of a cell.

Syntax
REGEXEXTRACT(text, regular_expression)

text – The input text
regular_expression – The first part of the text that matches this expression will be returned.

Sample Usage
=REGEXEXTRACT(A2,"..")
//Extracts the first two letters from the text in cell A2

=REGEXEXTRACT(A2,"...$")
//Extracts the last letter of the string

=REGEXEXTRACT(A2,"\w+")
//Extracts the first word of the string

Use RegEx in Google Sheets in a Few Steps

Learn how to use the REGEXEXTRACT function in Google Sheets–and its variations.

Why use the REGEXEXTRACT Function in Google Sheets?

The REGEXEXTRACT function in Google Sheets can be used to extract substrings of a cell. You can use REGEXEXTRACT to obtain any type of substring from your cell of interest.

Let’s say you have a column of date and time, as shown below:

If you want to extract only the dates from each of the cells and then filter the column based on dates. Then you can use the REGEXEXTRACT function in Google Sheets to specifically extract the dates to a separate column and then use ARRAYFORMULA to filter Date timings based on the dates extracted.

Syntax

The formula for the REGEXEXTRACT function in Google Sheets is:

=REGEXEXTRACT(text, regular_expression)

• text – The input text
• regular_expression – The first part of the text that matches this expression will be returned.

Note:
The REGEXEXTRACT Function in Google Sheets only works with text (not numbers) as input and returns text as output. If a number is desired as the output, try using the VALUE function in conjunction with this function. If numbers are used as input, convert them to text using the TEXT function.

REGEXEXTRACT function to Extract First Letters of a String.

Let’s say you have a column of names as shown below:

You want to extract the first 2 letters of every name in the column for creating name IDs.

You can use the following formula:

=REGEXEXTRACT(A2,"..")

• Here, the first parameter selects the cell of interest.
• The second parameter is the regular expression. The dots represent individual letters that need to be extracted. 
• Since we want only the first two letters of every name, we have used only two dots, one dot for every letter.
• The output will be:

REGEXEXTRACT function to Extract Last Letters of a String.

Let’s say you have a column of names as shown below:

You want to extract the last 3 letters of every name in the column for creating name IDs.

You can use the following formula:

=REGEXEXTRACT(A2,"...$")

• Here, the first parameter selects the cell of interest.
• The second parameter is the regular expression. The dots represent individual letters that need to be extracted. 
• Since we want only the last three letters of every name, we use three dots, one dot for every letter.
• At the end, we use a “$” sign to specify that we want to extract the last letters of the string.
• The output will be:

REGEXEXTRACT function to Extract the First Word and Last Word of a String.

Let’s say you have a column of strings as shown below:

You want to extract the first word of every cell in the column.

You can use the following formula:

=REGEXEXTRACT(A2,"\w+")

• Here, the first parameter selects the cell of interest.
• The second parameter is the regular expression. “\w” is used to extract alphanumeric characters from a string.
• Since we want the entire first word, we combine “\w” with a “+” to extract all the letters before a space.
• The output will be:

Similarly, you can extract the last word of a string using:

=REGEXEXTRACT(A2,"\w+$")

REGEXEXTRACT function to Extract Date from a String

• Let’s say you have a column of date-time as shown below:

You want to extract the first word of every cell in the column.

You can use the following formula:

=REGEXEXTRACT(A1,"\d{2}/\d{2}/\d{4}")

• You want to extract only the dates from the cells.
• Here, the first parameter selects the cell of interest.
• The second parameter is the regular expression. “\d” is used to extract numbers from a string.
• The number in the parentheses represents numbers up to which we are extracting.
• Since there is a “/” between months, days and year, we have to specify the digits up to which we want to extract.
• The output will be as shown below:

REGEXEXTRACT function to Extract Words Starting and Ending with Specific Letters from a String

Let’s say you have a column of some sentences as shown below:

You want to extract the first word of every cell in the column.

You can use the following formula:

=REGEXEXTRACT(A1, "br\w+d")

You want to extract words that start and end with specific letters from the cells.

• Here, the first parameter selects the cell of interest.
• The second parameter is the regular expression. We start the regex with br since we want to extract words starting with “br.
• “\w+” is used to extract all the alphanumeric characters after “br”.
• The regex ends with “d” as we want all words ending with d.
• The output will be as shown below:

See Also

You can check out other equally good articles on Google Sheets here.

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.

Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.

Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets

How to use Filter views in Google Sheets | Simple 2-min Guide 💻: Learn how to use Filter view in Google Sheets and its variations.