How to use REGEX formulas in Google Sheets

REGEX formula is a great function created for Google Sheets. You will not find this function in MS EXCEL, you have to write a VBA script to apply regex operations in Excel. Let’s learn How to use REGEX formulas in Google Sheets.

REGEX means a regular expression of a text string or integer or any data type. You can do multiple things with REGEX, create patterns, extract specific text or numeric digits, replace the text string, and much more.

There are three in built functions for REGEX in Google Sheets

  • REGEXEXTRACT
  • REGEXREPLACE
  • REGEXMATCH

REGEXEXCTRACT Examples:

How to Extract only words using REGEXEXTRACT

In the below example, in Column A there is a list of products, but it has some numeric values as well but you want to extract only Product name without any numeric values. You can use the below formulas to accomplish this task.

Formula:

=REGEXEXTRACT(A2,"\D+")
Regexextract in Google Sheets
=TRIM(REGEXEXTRACT(A2,"\D+"))

Explanation:

The regex term “\D” indicates ‘non-digits’ characters and “+” indicates the length of a text string. Hence, we are saying extract all the non-digits characters in the cell. Please remember “D” is case sensitive.

Also, it’s a good idea to use “TRIM function when you run “REGEX” formula to remove leading white spaces

How to Extract only numeric values using REGEXEXTRACT

Let’s put it other way around. Now you want only numeric values. To do so, you just need to change Capital “D” to small “d” only. See below formula:

=REGEXEXTRACT(A2,"\d+")
Regexextract in Google Sheets

Numeric values successfully extracted, but there is one problem. Formula converted them to a text string. Now, how you can change them to “Values”. To do that, just wrap them under “Value” formula:

=Value(REGEXEXTRACT(A2,"\d+"))
extract only numbers in Google sheets

How to Extract only words from a text string without special characters and numeric values using REGEXEXTRACT and REGEXREPLACE

Let’s go to one level up for REGEX formulas in Google Sheets. What if you have special characters also in a cell but you want to extract only words. You can use the below formula:

=TRIM(REGEXREPLACE(REGEXEXTRACT(A2,"\D+"),"[=,@-]",""))

Explanation:

In the above formula, first, we have extracted text string without numeric values using REGEXEXTRACT and then Replace all special characters with ‘none’ using REGEXREPLACE. And the used TRIM function to remove leading white spaces.

How to extract only last word using REGEXEXTRACT

Formula:

=TRIM(REGEXEXTRACT(A2,"\S+$"))
extract last word in google sheets

Explanation:

The regex term “\S” indicates ‘non-whitespace’ characters and “$” indicates at the end of the text. Hence, we are saying extract the last word before the whitespace. Please remember “S” is case sensitive.

How to extract email address from a text string using REGEXEXTRACT

=REGEXEXTRACT(A2,"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}")
extract email address

You get the Idea!

REGEXREPLACE Examples:

How to replace text in brackets using REGEXREPLACE

=REGEXREPLACE(A2,"\((.*)\)","(sometext)")
REGEXREPLACE in google sheets

How to replace text other than specified text using REGEXREPLACE

In the below text string (see below image) everything is replaced except product code

=REGEXREPLACE(A2,"\w+\s\w+\s\w+:","")
replace text in google sheets

Explanation:

The regex term “\w” indicates word character and “\s” indicates white space character. Hence, we are saying replace all characters before the semicolon(:).

How to replace extra comma and whitespace using RGEXREPLACE

In the below text string (see below image) all the extra comma and extra whitespace are replaced with a null value except for the required comma in the middle

=TRIM((REGEXREPLACE(A2,"\B,","")))
replace extra comma and whitespace

Explanation:

The regex term “\B” indicates “not a word boundary”. Hence, we are saying replace all comma which does not have any word boundary then used TRIM function to remove extra white space.

REGEXMATCH Examples:

REGEXMATCH is a great function but you will not get the best result when you apply it standalone. It will only give you True or False in that case. REGEXMATCH is more useful when applied with other functions like FILTER or TEXTJOIN or any other function for that matter. Let’s see the below example:

How to create a dynamic filter search using REGEXMATCH in Google Sheets

In the below image in column A, there is a product list for laptops, and in column B their price. As you can see the product text string is very long and it’s difficult to search a particular model based on Brand or Size. Now, what you can do, you can create a dynamic search using REGEXMATCH formula and as you entered your search criteria in cell ‘E1″ the data will be filtered magically.

=FILTER((A4:B),REGEXMATCH(PROPER(A4:A),PROPER(E1)))
Regexmatch in google sheets

You can access full REGEX (RE2 regular expression syntax reference) here

Read More:

How to Remove Unwanted Text From Multiple Columns In Google Spreadsheet

How to send email notifications on Google Spreadsheets if any cell value changes

COMMENTS