What is the REGEXMATCH function in Google Sheets?
- The Google Sheets REGEXMATCH function allows you to quickly test whether the contents of a cell includes a specific string or pattern. It returns a “TRUE” if the text matches and a “FALSE” if it doesn’t.
- A REGULAR EXPRESSION is a text pattern or sequence of characters, including symbols, letters, and numbers that are combined to form a search instruction or formula. It often contains certain special symbols, also known as ‘metacharacters’, that assist in recognizing specific characters, strings, or patterns in a text string.
- METACHARACTERS are used in the formula to represent either a single character, a sequence of characters, or one of the characters in a set. See the table below for examples.
- The flexibility of this formula provides a powerful tool for sorting and categorizing large datasets, identifying keywords and selecting specific records..
- The REGEXMATCH function belongs to Google Sheets’ suite of REGEX functions that includes REGEXEXTRACT and REGEXREPLACE.
Syntax
=regexmatch(text, reg_exp)
text
is the string or value to be tested for whether it matches the test expression. This function easily scans large files of data, where the text values are compiled in column A of a spreadsheet.
reg_exp
is the test instruction (in the form of a regular expression), used in this Google Sheets function. When this formula is copied down column B, adjacent to the text values, it returns either the value “TRUE” or “FALSE”, thus indicating whether the text matches the pattern of the reg_exp or not.
- The term reg_exp in the formula sounds threatening and may discourage novice users, but numerous useful applications exist once the use of metacharacters is mastered.
- The reg_exp serves as an instruction written in code using defined syntax, that controls how text strings should be evaluated.
Examples of practical applications:
- A few simple examples are given below illustrating how the REGEXMATCH function can save time and increase efficiency.
- In each case a Regular Expression is formulated according to the format of the test expression.
- The number of records processed by the REGEXMATCH function is limited only by the size of the data file.
- In fact, one may discover that once proficient in deploying the power of the REGEXMATCH function and regular expressions, there are limitless opportunities.
- Click on this link to see examples in Google Sheets: Examples using REGEXMATCH function
Example 1.0-Identify cells in a range that contain a particular letter, word or phrase.
- Check whether the cell A1 contains the word “yes”
Format the regular expression as follows: =REGEXMATCH(A1,"yes")
Example 1.1-Identify cells in a range that contain any of several particular words.
- Determine whether cell A1 contains any of the words ‘jolly’, ‘fine’ or ‘splendid’.
Format the reg_exp as follows: =REGEXMATCH(A1, "jolly|fine|splendid")
Example 1.2-Identify cells in a range that contain numbers
- Determine whether a cell contains numbers between 0-9.
Format the reg_exp with square brackets as shown as follows: =REGEXMATCH(A1, "[0-9]") so that the digits 0-9 are seen as characters and not numbers.
Note: The REGEXMATCH function is designed to analyze text strings, so it will not work if the first character in the cell is a number.
Example 2.0-Identify cells starting with a specific text string
- Determine whether a cell begins with the word “start” or “Start”
Format a reg_exp using the metacharacters ‘^’ and ‘|’ to represents the beginning of a string and the Or operator: =REGEXMATCH(A1, "^start|Start")
Example 2.1-Identify cells ending with a specific text string
- Determine whether a cell ends with “start or Start”
Format a reg_exp using metacharacters ‘$’ the ‘|’ to represent the end of a string and the OR operator: =REGEXMATCH(A1, "start|Start$")
Example 2.2-Identify cells that start and end with 2 specific text strings
- Determine the first and last word in a cell
Format a reg_exp using a combination of both ‘^’ and ‘$’ metacharacters:to select only those records where these are “Start” and “stop”
- Ignore any characters or spaces between the first and last words
Include “[a-zA-Z ]+” and use the formula =REGEXMATCH(A1, "^Start[a-zA-Z ]+stop$").
The space after the ‘Z’ allows for spaces to be included and the ‘+’ sign represents any number of letters and or spaces.
Example 3.0-Identify the Exact Match of a String
- Determine whether the text string in cell A1 perfectly matches the string “word for word”
Format a reg_exp as follows: =REGEXMATCH(A1,"^word for word$")
Example 4-Identify Hashtags among Social Media Posts
- Determine which records in a spreadsheet of social media posts contains hashtags
Format a reg_exp as follows: =REGEXMATCH(A1,(“#”))
Example 5-Identify email addresses in a spreadsheet
- Format a reg_exp starting with: ^[a-zA-Z0-9.-_]+
Determine that the cell begins with a string of letters, numbers, dot operators, hyphens, and/or underscores
- Next check for the ‘@’
- Then: [a-zA-Z0-9]+
because next follows another string containing letters and/ or numbers
- Then: \.
because next follows a dot operator (.) The ‘\’ metacharacter precedes the dot operator (.) to avoid it being mistaken for the dot metacharacter
- Finally: [a-zA-Z]+$
because email addresses all end with a string of letters: =REGEXMATCH(A4,"^[a-zA-Z0-9.-_]+@[a-zA-Z0-9]+\.[a-zA-Z]+$")
Example 6-Use this function to refine Filter Criteria in Google Sheets
- The regular expression will be: =REGEXMATCH(A1:A, "Olive Oil|Coconut Oil")
because we want data relating only to Olive Oil and Coconut Oil.
- Combine this with the Filter function: =filter(A1:D, REGEXMATCH(A1:A, "Olive Oil|Coconut Oil"))
The selected data will be presented in a new table in columns 5 - 8
Important reminders:
- The function only works with text input. It does not work with numbers.
- If you want to use numbers as input (for example telephone numbers), you need to first convert them to text, using the Google Sheets TEXT function.
- The function is case-sensitive. Therefore, you will need to specify the correct case inside the regular expression or convert the entire input string to upper or lower case using the UPPER or LOWER functions.
- Various online tools exist to assist with formulating and testing regular expressions because this can be quite tricky.
Recommended articles
More resources
