How to Use Excels Regex Function to Power Up Your Searches

Filtering and searching in Excel is no easy task. Regex functions change that. Now you can specify exactly what you need—complex patterns, partial matches, or extracting structured data—without the effort.

Table of Contents

• What is Regex?
• Search for patterns
• Additional data using Regex
• Find and Replace with Regex
• Combining Regex with other functions

What is Regex?

Regex is a type of pattern used to search a string of text or characters for a match. Have you ever wondered how websites can tell you that the email pattern you entered on the login page is invalid? That's an example of a regex pattern using an email signature in action.

Regular expressions aren't unique to Excel — they're available in many text editors, programming languages, command-line tools, IDEs, and even Excel's competitor, Google Sheets .

Regex can seem complicated, and it can be 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 basic symbols and patterns. This guide will keep it as simple as possible so you can get started.

The following are the symbols that will be used in this guide:

Simple regular expression patterns that you can build using these symbols include:

Regex functions are predefined Excel formulas that can be used to define a pattern for searching and manipulating text strings. There are currently three regex functions. We will see how to use them individually and with other functions.

Search for patterns

The first function we'll look at is REGEXTEST. This function takes a text string that you want to use for searching and a regex pattern, then uses the latter pattern to find a match in the former pattern. The function will return True or False.

The syntax of the REGEXTEST function is as follows:

REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

The first two parameters, string_to_search and regex_pattern_to_use, are self-explanatory. The [case_sensitivity] parameter is optional - anything in square brackets when talking about Excel syntax is optional - and indicates whether you want the search to be case sensitive (0) or case insensitive (1). The default is case sensitive.

The example will use REGEXTEST to see if the user entered a valid email address using the following formula:

REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

Here, we are searching in cell B3 to see if it contains an email address using the regular expression pattern below:

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

If you put the formula in cell C3 and enter [email protected] in cell B3, the formula will return True because it matches the email signature.

Additional data using Regex

Next, let's look at the REXEXEXTRACT function. This function returns a substring (a portion of a string) that matches the provided regex pattern.

The syntax of the REXEXEXTRACT function is as follows:

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

Continuing with the email example, let's add a formula to cell B4 to extract the username of the email part.

The formula would look like this:

=REGEXEXTRACT(B3, "([^@]+)")

In this formula, we extract everything before the @ symbol in the email address entered in B3.

Find and Replace with Regex

The last regex function we'll look at is REGEXREPLACE. This function is similar to Excel's REPLACE function, but it also supports RegEx. It takes the text string you want to modify and checks to see if any substrings match the specified regex pattern. If one is found, it replaces that string with the provided replacement string.

The syntax of the REGEXREPLACE function is as follows:

REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

Here are the important parameters to note in this function:

• string_to_modify : The text string you want to modify.
• replacement_string : String to replace the substring with.
• number_of_occurrences : The exact instances you want to replace.

Here's an example of using the function to replace the username portion of an email with another text string:

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

The value of B3 is [email protected] and after we enter the above formula in cell C3, it will return [email protected].

Combining Regex with other functions

You can also combine regex functions with other functions in Excel. For example, you can combine the REGEXTEST function with Excel's IF statement and display appropriate messages based on the results.

Here is an example formula:

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

This formula uses an IF statement to check if the email address entered in cell B6 is valid and then displays This is a valid email address! if it is TRUE or The email address is invalid! if it is FALSE . Additionally, you can pair this formula with the FIND function to quickly find data in Excel.

This is a great way to get started using RegEx in Excel. The use cases and possibilities are only limited by your imagination.