Quick Links
Filtering and searching in Excel can feel like a guessing game. Regex functions change that. Now, I can pinpoint exactly what I need—complex patterns, partial matches, or structured data extraction—without breaking a sweat.
What Is Regex?
Regex is a type of pattern used to search a text string or sequence of characters for a match. Have you ever wondered how websites can tell you that the email pattern you have entered on a login page is invalid? That’s one example of a regex pattern using an email signature at work.
Regular expressions are not unique to Excel—they’re available in multiple text editors, programming languages, command-line tools, IDEs, and even Excel’s competitor, Google Sheets.
Regex might sound complicated, and it is if you want to use it to its full potential, but you don’t need to be a programmer to use it effectively. In some cases, you can get away with just knowing how to use a few symbols and basic patterns. I’ll keep this guide as simple as possible so you can start using them.
Here are the symbols we’ll be using in this guide:
Description
Specifies a range of characters within brackets.
Match the start of a string.
Match the end of a string.
Match any character other than the new line character.
Match 0 or more of the preceding character.
Match 1 or more of the preceding characters.
Group the matched characters into one.
Match any of the characters inside the brackets.
Match any character that is not inside the brackets.
Match exactly n instances of the preceding character.
Match n or more occurrences of the preceding character
Simple regex patterns you can build with these symbols include:
Regex Pattern
Match a single digit from 0 to 9
[a-zA-z0-9]
This is a combination range that matches a single character from lowercase a to z, uppercase A to Z, and from 0 to 9.
Match any string that begins withpro.
Match any character that is not$.
Group the patterncon.
Match 3 or more occurrences of the lattera(e.g.,a,aa, oraaa).
Regex functions arepredefined Excel formulasthat can be used to define a pattern for searching and manipulating text strings. There are three regex functions as of now, and we’re going to look at how to use them separately and with other functions.
Search for Patterns
The first function we are going to look at is REGEXTEST. It takes a text string you want to use for searching and a regex pattern, and uses the latter to find a match in the former. The function will return eitherTrueorFalse.
The syntax for the REGEXTEST function is as follows:
The first two parameters,string_to_searchandregex_pattern_to_use, are self-explanatory. The[case_sensitivity]parameter is optional—anything placed in square brackets when talking about Excel syntax is optional—and denotes whether you want the search to be case-sensitive (0) or case-insensitive (1). The default is case-sensitive.
In our example, we will use REGEXTEST to see if a user has entered a valid email address using the following formula:
Here, we are searching in cellB3to see if it contains an email address using the regex pattern below:
If I place the formula in cellC3and enterjohn.doe@example.comin cellB3, it will returnTruebecause that matches the signature of an email.
Extra Data Using Regex
Next, lets look at the REXEXEXTRACT function. It returns a substring (a portion of the string) that matches the regex pattern provided.
The syntax for the REXEXEXTRACT function is as follows:
Continuing with the email example, let’s add a formula in cellB4to extract the username of the portion of the email.
Here is what the formula will look like:
In this formula, we extract everything before the@symbol in the email address entered inB3.
Find and Replace Using Regex
The final regex function we will look at is REGEXREPLACE. This function is similar toExcel’s REPLACE function, but also supports RegEx. It takes the text string you want to modify and checks to see if there is a substring that matches the defined regex pattern. If it finds it, it replaces it with the provided replacement string.
The syntax of the REGEXREPLACE function is as follows:
Here are the important parameters to pay attention to in this function:
Here is an example of using the function to replace the username portion of the email with another text string:
The value ofB3isjohn.doe@example.com, and after we enter the formula above in cellC3, it will returnjane.doe@example.com.
Combine Regex With Other Functions
you may also combine the regex functions with other functions in Excel. For instance, you can combine the REGEXTEST function with anExcel IF statementand display the appropriate message based on the result.
Here is an example formula:
This formula uses the IF statement to check if the email address entered in cell B6 is valid and then displayThis is a valid email address!if it isTRUEorThe email address is invalid!if it isFALSE. Alternatively, you could pair this with the FIND function toquickly find data in Excel.
This is not meant to be an extensive guide on regular expressions—that would require several articles on its own. However, it’s a good way to start using RegEx in Excel. The use cases and possibilities are only limited by your imagination.
