How to Use Regex in Excel: A Beginner’s Guide

Regular expressions (regex) are powerful tools for text processing that allow you to match, extract, and replace text based on specific patterns. Excel doesn’t natively support regex, but you can use third-party add-ins to bring this functionality to your spreadsheets. One popular option is the “Regex Tools” add-in.

These new regex functions in Excel offer versatile ways to search and manipulate text data. REGEXTEST checks if supplied text matches a regex pattern, while REGEXEXTRACT extracts parts of text that match the pattern, and REGEXREPLACE replaces matching text with new content By mastering these functions, you can unlock more efficient data cleaning, formatting, and text processing in Excel.

Getting Started with Regex in Excel

Basics of Regex Syntax and Patterns

A regular expression (aka regex or regexp) is a specially encoded sequence of characters that defines a search pattern. Using that pattern, you can find matching character combinations in a string or validate data input. If you are familiar with wildcard notation, you can think of regexes as an advanced version of wildcards.

Regular expressions have their own syntax consisting of special characters, operators, and constructs^]. For example, [0-5] matches any single digit from 0 to 5.

Regular expressions can contain:

1. Literal characters: These match themselves. For instance, the regex hello would match the string hello exactly.
2. Metacharacters: These have special meanings and include . (dot), *, +, ?, | (pipe), () (parentheses), [] (square brackets), ^ (caret), $, and \\ (backslash).
3. Character classes: Defined within square brackets [...], these allow you to match any one character from a set. For example, [aeiou] matches any vowel, and [A-Z] matches any uppercase letter.
4. Quantifiers: These specify how many times a pattern, character, or character class must occur to achieve a match. For instance, a{3} matches exactly three consecutive “a” characters, and ab? matches either “a” or “ab”.
5. Anchors: These special characters do not match any character in the string but match a position before, after, or between characters. The ^ anchors the pattern to the start of the string, and $ anchors it to the end.
6. Modifiers: These characters change how the regex engine interprets the pattern, affecting aspects like case sensitivity, multiline matching, and how special characters are interpreted. For example, i makes the pattern case-insensitive, and g makes it global, matching all occurrences in the input.

Cheat Sheet with Common Regex Patterns

The following table provides a quick reference to the main regex patterns, which can help you grasp the basics and serve as a cheat sheet when studying further examples.

Pattern	Legend	Example	Sample Match
.	Matches any character except newline	a.c	abc
\d	Matches any digit character	\d\d\d	123
\D	Matches any non-digit character	\D\D\D	abc
\w	Matches any word character (alphanumeric & underscore)	\w\w\w	a1_
\W	Matches any non-word character	\W\W\W	!@#
\s	Matches any whitespace character	A\sB	A B
\S	Matches any non-whitespace character	\S\S\S	abc
[xyz]	Matches any character in the set	[abc]	a,b,c
[^xyz]	Matches any character not in the set	[^abc]	d,e,f
^	Matches the start of the string	^Hello	Hello World
$	Matches the end of the string	World$	Hello World
\b	Matches a word boundary	\bWorld\b	Hello World
\B	Matches a non-word boundary	\BWorld\B	HelloWorld
x|y	Matches eitherxory	a|b	a,b
(xyz)	Capturesxyzin a group	(abc)\1	abcabc

This cheat sheet provides a solid foundation for understanding and working with regular expressions in Excel. While studying further examples, you can refer back to this table for a quick refresher on the basic regex patterns and their usage.

Using REGEXTEST Function

Explanation of the Function

The REGEXTEST function in Excel is used to test whether a given text string matches a specified regular expression pattern. It returns a Boolean value of TRUE if the text string contains a match for the regular expression pattern, and FALSE otherwise.

The syntax for the REGEXTEST function is:

REGEXTEST(text, regular_expression)

Where:

• text is the text string that you want to search for a pattern match.
• regular_expression is the regular expression pattern that you want to match against the text string.

The REGEXTEST function is particularly useful when you need to validate or check if a text string conforms to a specific pattern. For example, you can use it to verify if an email address or phone number is in the correct format.

Examples of using REGEXTEST

Here are a few examples to illustrate the usage of the REGEXTEST function:

1. Checking if a text string contains digits

To check if a text string contains at least one digit, you can use the following regular expression pattern: \d

=REGEXTEST("Hello123", "\d") // Returns TRUE
=REGEXTEST("HelloWorld", "\d") // Returns FALSE

2. Validating email addresses

You can use a regular expression pattern to validate if a text string is a valid email address format:

=REGEXTEST("[email protected]", "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b") // Returns TRUE
=REGEXTEST("invalid@email", "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b") // Returns FALSE

3. Checking for specific character patterns

You can use the REGEXTEST function to check if a text string contains a specific pattern of characters:

=REGEXTEST("The quick brown fox", "quick.*fox") // Returns TRUE
=REGEXTEST("Hello World", "quick.*fox") // Returns FALSE

In the above examples, the regular expression pattern quick.*fox matches any text string that contains the word “quick” followed by any characters (represented by .*), and then the word “fox”.

By combining the REGEXTEST function with other Excel functions and formulas, you can perform powerful data validation, text processing, and data cleaning tasks. It provides a flexible and efficient way to work with text data in Excel.

How to Use REGEXEXTRACT Function

Explanation of the Function

The REGEXEXTRACT function in Excel is used to extract a substring from a text string based on a specified regular expression pattern. It takes two required arguments:

1. text: This is the text string from which you want to extract the substring.
2. regular_expression: This is the regular expression pattern that defines the substring you want to extract.

The REGEXEXTRACT function searches the text for a match to the regular_expression pattern and returns the first substring that matches the pattern. If no match is found, the function returns an empty string.

Syntax

The syntax for the REGEXEXTRACT function is:

REGEXEXTRACT(text, regular_expression, [return_mode], [case_sensitivity])

Where:

• text (required): The text or the reference to a cell containing the text you want to extract strings from.
• regular_expression (required): The regular expression (“regex”) that describes the pattern of text you want to extract.
• return_mode (optional): A number that specifies what strings you want to extract. By default, the return mode is 0. The possible values are:
  • 0: Return the first string that matches the pattern.
  • 1: Return all strings that match the pattern as an array.
  • 2: Return capturing groups from the first match as an array.
• case_sensitivity (optional): Determines whether the match is case-sensitive. By default, the match is case-sensitive. Enter one of the following:
  • 0: Case-sensitive.
  • 1: Case-insensitive.

Capture Groups

It is possible to return multiple results with capture groups. A capture group is a part of a pattern that can be enclosed in parentheses. If there are no capture groups, the function returns the whole match.

Examples of using REGEXEXTRACT

Extract names based on capital letters Data:

DianaWalters

Formulas:

=REGEXEXTRACT(A2, “[A-Z][a-z]+”)
=REGEXEXTRACT(A2, “[A-Z][a-z]+”, 1)
The regular expression pattern "[A-Z][a-z]+" matches names starting with a capital letter followed by lowercase letters.

Extract phone numbers based on their structureData:

Sonia Simone(378) 555-4195
Angela Breen (878) 555-8322
Blake Masters (437) 555-8187
William Kosby (619) 555-5212
Avana Smith (579) 555-3658
Patrick Jones (346) 555-1925
Lionel Ranier(405) 555-7887
Hannah Rogers (666) 555-4872
Formula:

REGEXEXTRACT(A2, “[0-9()]+\s[0-9-]+”, 1)
The regular expression pattern "[0-9()]+\s[0-9-]+" matches phone numbers in the format of a sequence of digits and parentheses, followed by a space and another sequence of digits and hyphens.

By combining the REGEXEXTRACT function with other Excel functions and formulas, you can perform powerful text extraction and data cleaning tasks. The ability to define complex patterns using regular expressions makes it a versatile tool for working with text data in Excel.

How to Use REGEXREPLACE Function

Explanation of the Function

The REGEXREPLACE function in Excel is used to replace parts of a text string with a different text string based on a specified regular expression pattern. It searches the input text for values that match the regular expression and replaces the found matches with the replacement text specified.

The syntax for the REGEXREPLACE function is:

REGEXREPLACE(text, regular_expression, replacement, [instance_num], [case_sensitivity])

Where:

• text (required) is the text or the reference to a cell containing the text you want to replace strings within.
• regular_expression (required) is the regular expression (“regex”) that describes the pattern of text you want to replace.
• replacement (required) is the text you want to replace instances of the pattern with.
• instance_num (optional) specifies which instance of the pattern you want to replace. By default, it is 0, which replaces all instances. A negative number replaces that instance, searching from the end.
• case_sensitivity (optional) determines whether the match is case-sensitive. By default, the match is case-sensitive. Enter 0 for case-sensitive or 1 for case-insensitive.

Examples of using REGEXREPLACE

1. Anonymizing phone numbers

To anonymize phone numbers by replacing their first three digits with ***, you can use the following pattern: "[0-9]+-"

Data:

Sonia Simone(378) 555-4195
Angela Breen (878) 555-8322
Blake Masters (437) 555-8187
William Kosby (619) 555-5212
Avana Smith (579) 555-3658
Patrick Jones (346) 555-1925
Lionel Ranier(405) 555-7887
Hannah Rogers (666) 555-4872

Formula:

=REGEXREPLACE(A2, "[0-9]+-", "***-")

The pattern [0-9]+- matches any sequence of digits followed by a hyphen, which is then replaced with ***-

2. Separating and reordering names

You can use REGEXREPLACE with capturing groups to separate and reorder given names and last names. For example, to swap the order of first and last names, you can use the pattern "([A-Z][a-z]+)([A-Z][a-z]+)" and the replacement "$2, $1"

Data:

SoniaBallard

Formula:

=REGEXREPLACE(A2, "([A-Z][a-z]+)([A-Z][a-z]+)", "$2, $1")

In this example, ([A-Z][a-z]+) defines the first capturing group for the first name, and ([A-Z][a-z]+) defines the second capturing group for the last name. The replacement "$2, $1" swaps the order by referencing the second and first capturing groups, respectively.

By combining the REGEXREPLACE function with other Excel functions and formulas, you can perform powerful text manipulation and data cleaning tasks. The ability to define complex patterns using regular expressions makes it a versatile tool for working with text data in Excel.

Regex Integration with XLOOKUP and XMATCH

Upcoming regex support in XLOOKUP and XMATCH functions

Microsoft Excel will soon introduce the ability to use regular expressions (regex) within the XLOOKUP and XMATCH functions, providing a powerful new way to perform pattern matching and lookups. This will be achieved through a new option for the ‘match mode’ arguments of these functions, where the regex pattern will be supplied as the ‘lookup value’

With this upcoming feature, users will be able to leverage the full potential of regular expressions when performing lookups and matches in Excel. The regex pattern specified as the ‘lookup value’ will be used to search for and match corresponding values or patterns within the lookup array or range.

For example, instead of searching for an exact value, you could use a regex pattern to match a range of values that follow a specific pattern, such as phone numbers or email addresses. This will greatly enhance the flexibility and versatility of the XLOOKUP and XMATCH functions, allowing for more sophisticated data analysis and manipulation.

While the exact implementation details are yet to be revealed, Microsoft has confirmed that this regex integration with XLOOKUP and XMATCH will be available for users to try in the Beta version soon. Once the feature is released in the Beta, Microsoft plans to update their documentation and provide more detailed information on how to effectively utilize this new capability.

The introduction of regex support in XLOOKUP and XMATCH functions is a significant step forward in expanding Excel’s text processing and data manipulation capabilities. It will allow us to perform more complex lookups and matches, streamline various data analysis tasks and enhance overall productivity.

Tips and Tricks

Common regex tokens and patterns

When writing regex patterns, you can use symbols called ‘tokens’ that match with a variety of characters. Here are some useful tokens to get you started:

• [0-9]: Matches any numerical digit.
• [a-z]: Matches a character in the range of a to z.
• .: Matches any character.
• a: Matches the literal character “a”
• a*: Matches zero or more occurrences of the character “a” 
• a+: Matches one or more occurrences of the character “a” 

Regular expressions can contain literal characters that match themselves. For example, the regex hello would match the string hello exactly.

Metacharacters are special characters in regular expressions that have specific meanings. Some common metacharacters include:

• . (dot): Matches any single character except a linebreak.
• *: Matches zero or more occurrences of the preceding character or group.
• +: Matches one or more occurrences of the preceding character or group.
• ?: Matches zero or one occurrence of the preceding character or group.
• | (pipe): Acts as a logical OR and allows you to specify alternatives.
• () (parentheses): Groups characters or subpatterns together.
• [] (square brackets): Defines a character class, allowing you to match any one character from a set of characters.
• ^ (caret): Matches the start of a line or string.
• $: Matches the end of a line or string.
• \\ (backslash): Escapes a metacharacter to match it literally.

Character classes in regular expressions are special notations that allow you to match any one out of a set of characters. They are defined using square brackets [...]. For example, [aeiou] matches any vowel, and [A-Z] matches any uppercase letter.

Quantifiers are constructs that specify how many times a particular pattern, character, or character class must occur in the target string to achieve a match. For instance, a{3} matches exactly three consecutive “a” characters, and ab? matches either “a” or “ab” 

Anchors are special characters that match a position before, after, or between characters, rather than matching actual characters. The ^ anchor matches the start of the string, and $ anchors the pattern to the end.

Modifiers are characters that change how the regex engine interprets the pattern. For example, i makes the pattern case-insensitive, and g makes it global, matching all occurrences in the input.

Using Bing Copilot for regex patterns

Bing Copilot can be a helpful tool when working with regular expressions in Excel. If you’re unsure about a specific regex pattern or need assistance in constructing one, you can ask Bing Copilot for suggestions. The AI-powered assistant can provide you with regex patterns based on the context and requirements you describe.

However, you’ll need to review the formula suggestions provided by Copilot AI carefully. These suggestions are based on the context and may need to be adjusted or refined to suit your specific needs. Regularly using the data cleaning feature can also help ensure data accuracy and improve the performance of AI-driven features like Copilot.

Additionally, Copilot can offer visualization suggestions that may lead to better data representation and more insightful analysis. It’s recommended to experiment with different visualization options provided by Copilot to find the most suitable representation for your data.

Lastly, Copilot can also provide error suggestions, which can help you identify and correct any mistakes or inconsistencies in your spreadsheets. Regularly review and address these error suggestions to ensure that your spreadsheets remain accurate and reliable.

Note:

It’s important to note that when using the REGEXEXTRACT function, the results are always returned as text values. If you need to convert these extracted text values back to numbers, you can use the VALUE function in Excel.

Conclusion

The introduction of regex functions like REGEXTEST, REGEXEXTRACT, and REGEXREPLACE marks a significant advancement in Excel’s text processing capabilities. You can use these regular expressions to perform intricate pattern matching, data validation, and text manipulation tasks with efficiency. These functions open up new possibilities for data cleaning, formatting, and analysis, streamlining workflows and enhancing productivity.

FAQs

How Do I Activate Regular Expressions (RegEx) in Excel?

To utilize RegEx in Excel, you must have access to the appropriate functions or add-ins that support regular expressions. Excel does not have built-in RegEx support by default, so you might need to use VBA (Visual Basic for Applications) or a third-party add-in that enables RegEx functionality.

What Are Some Effective Ways to Quickly Learn Regular Expressions (RegEx)?

To rapidly master Regular Expressions, follow these six steps for an efficient learning curve:

1. Begin your journey with RegexOne to grasp the basics.
2. Look for easy-to-understand documentation to deepen your understanding.
3. Explore tools like RegEx Pal to practice and refine your skills.
4. Test what you’ve learned through practical exercises.
5. Challenge yourself with Regex Crosswords to enhance problem-solving skills with RegEx.
6. Consistently practice to solidify your knowledge and skills in using regular expressions.

References: Datacamp, Ablebits.com, Microsoft