Get the Regex Workbook
Includes the cheat sheet, practice data, and final solutions.
Extract emails, dates, and URLs on your own.
No spam. Unsubscribe anytime.
What Is Regex in Excel?
Regex, or regular expressions, is a tool that finds patterns in text. It searches, matches, and replaces text based on defined patterns. This tool is widely used in programming, data analysis, and text processing.
But it’s not just for programmers. Anyone using Excel can benefit from it. With AI, regex is easy to use, even for beginners.
Regex in Excel helps you clean data, validate entries, and format text quickly. It saves you time and makes handling large datasets easier.
In this article, we’ll explore two Regular Expression Functions in Excel with practical examples.
- REGEXEXTRACT: Extracts one or more parts of supplied text that match a regex pattern.
- REGEXREPLACE: Searches for a regex pattern within supplied text and replaces it with different text.
Download the workbook and start using regex in Excel today!
How Does REGEXEXTRACT Work in Excel?
The REGEXEXTRACT function in Excel extracts specific text from a string using patterns called regular expressions (regex). It can find and pull the first match, all matches, or parts of the first match.
For example, you can use REGEXEXTRACT in Excel to find email addresses, dates, or phone numbers in a list of text. This makes it easy to organize and clean your data quickly.
Syntax
The syntax for the Excel REGEXEXTRACT function is:
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])Arguments
- text (required): The text or cell reference containing the text you want to extract from.
- pattern (required): The regex pattern that describes the text you want to find.
- return_mode: A number that tells Excel what to extract. The default is 0.
- 0: Extracts the first match.
- 1: Extracts all matches as an array.
- 2: Extracts parts of the first match (capturing groups) as an array.
- case_sensitivity: Determines if the match is case-sensitive. The default is case-sensitive.
- 0: Case sensitive.
- 1: Case insensitive.
How Does REGEXREPLACE Work in Excel?
The REGEXREPLACE function in Excel lets you replace parts of a text string using patterns called regular expressions (regex). This is useful for cleaning up data or making consistent changes across a dataset.
Syntax
The syntax for the Excel REGEXREPLACE function is:
=REGEXREPLACE(text, pattern, replacement)Arguments
- text (required): The text or cell reference containing the text you want to change.
- pattern (required): The regex pattern that identifies the text you want to replace.
- occurrence: Specifies which instance of the pattern to replace. The default is 0, which replaces all instances. A negative number replaces that instance, searching from the end.
- case_sensitivity: Determines if the match is case-sensitive. The default is case-sensitive.
- 0: Case sensitive.
- 1: Case insensitive.
How Does REGEXTEST Work in Excel?
The REGEXTEST function checks whether any part of a text string matches a regular expression pattern. It returns TRUE if there is a match and FALSE if there is not.
REGEXTEST is the simplest of the three regex functions. Use it to validate data (check if a cell contains a valid email, phone number, or ID format) without extracting or replacing anything.
Syntax
The syntax for the Excel REGEXTEST function is:
=REGEXTEST(text, pattern, [case_sensitivity])Arguments
- text (required): The text or cell reference you want to test.
- pattern (required): The regex pattern that describes what you are looking for.
- case_sensitivity: Controls whether the match is case-sensitive. The default is case-sensitive.
- 0: Case sensitive.
- 1: Case insensitive.
REGEXTEST performs a “contains” check by default. If the pattern appears anywhere in the text, the function returns TRUE.
To test whether the entire string matches the pattern (not just part of it), add the ^ anchor at the start and the $ anchor at the end of your pattern.
Example 1: Validate Email Addresses
To check whether cell A2 contains a valid email format, enter this formula in cell B2:
=REGEXTEST(A2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")The ^ and $ anchors ensure the entire cell value must match the email pattern, not just a portion of it.
The formula returns TRUE for entries like john@example.com and FALSE for entries like “Contact john@example.com for details” (because the cell contains text beyond the email address).
If you want to check whether a cell contains an email anywhere in the text, remove the anchors:
=REGEXTEST(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")Example 2: Check for a Phone Number Pattern
To test whether a cell contains a phone number in the format ###-###-####, use this formula:
=REGEXTEST(A2, "\d{3}-\d{3}-\d{4}")This returns TRUE if the text contains a sequence like 555-123-4567 anywhere in the string.
Example 3: Use REGEXTEST Inside IF
REGEXTEST replaces the common ISNUMBER(SEARCH()) workaround. Instead of:
=IF(ISNUMBER(SEARCH("@", A2)), "Email found", "No email")You can write:
=IF(REGEXTEST(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"), "Email found", "No email")The REGEXTEST version is more precise. SEARCH only checks for the @ symbol, which would match entries like “Cost @ $5 each.” REGEXTEST validates the full email pattern.
For more ways to combine IF with text matching, see our guide to IF with partial text match in Excel.
Example 1: Extract Email from String
Imagine you have a table with various text entries, and you need to extract all email addresses into a separate cell next to the text.
Here is the Step-by-Step Guide:
Step 1: Basic Extraction with REGEXEXTRACT
To extract email addresses into column C, enter the following formula in cell C2:
=REGEXEXTRACT(B2, "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Z|a-z]{2,}\b", 1)Explanation of the Arguments
- B2: This is the cell reference containing the text from which you want to extract the email address.
- “\b[A-Za-z0-9.%+-]+@[A-Za-z0-9.-]+.[A-Z|a-z]{2,}\b”: This is the regex pattern that matches email addresses.
- 1: This optional argument specifies the return mode. In this case, it means ‘All Matches” to extract all email addresses in the text.
Step 2: Handling Multiple Email Addresses with TEXTJOIN
REGEXEXTRACT can extract multiple email addresses from a cell. However, if it finds more than one email address, it spills the result to the next row, causing ‘#SPILL!’ errors when dragging the formula down.
To fix this, wrap the REGEXEXTRACT formula inside the TEXTJOIN function. The TEXTJOIN function combines text from multiple cells or ranges with a chosen delimiter:
=TEXTJOIN(", ", TRUE, REGEXEXTRACT(B2,"\b[A-Za-z0-9.%+-]+@[A-Za-z0-9.-]+.[A-Z|a-z]{2,}\b", 1))Step 3: Handling No Matches with IFERROR
If there are no email addresses in the text, the formula returns a ‘#N/A’ error.
To handle this, wrap the formula inside the IFERROR function:
=IFERROR(TEXTJOIN(", ", TRUE, REGEXEXTRACT(B2, "\b[A-Za-z0-9.%+-]+@[A-Za-z0-9.-]+.[A-Z|a-z]{2,}\b", 1)), "")Use this combined formula to extract email addresses from column B. It puts them in column C without errors.
Featured Bundle
Black Belt Excel Bundle
Example 2: Extract Text Inside Parentheses
In this example, we’ll show you how to extract text inside parentheses from one column to another using the REGEXEXTRACT function.
Step 1: Basic Extraction with REGEXEXTRACT
To extract the text inside parentheses into column C, enter this formula in cell C2:
=REGEXEXTRACT(B2:B11, "(([^)]+))")
Step 2: Remove Parentheses from Results
To remove the parentheses from the extracted text, we’ll adjust the REGEXEXTRACT formula. Use the ‘return_mode’ argument set to 2, which captures groups of the first match:
=REGEXEXTRACT(B2:B11, "(([^)]+))", 2)
Example 3: Extracting Dates from String
We’ll show how to extract dates from text in Excel. Then, we’ll split them into columns for year, month, and day.
Step 1: Basic Extraction with REGEXEXTRACT
To extract the dates from the string in column B into separate columns for year, month, and day, use the REGEXEXTRACT function. Enter this formula in cell C2:
=REGEXEXTRACT(B2, "(\d{4})-(\d{1,2})-(\d{1,2})", 2)
Step 2: Handling Multiple Columns with TOROW
If you want to spill the extracted dates across a row instead of down a column, you can combine REGEXEXTRACT with the Excel TOROW function. Use this formula:
=TOROW(REGEXEXTRACT(B2, "(\d{4})-(\d{1,2})-(\d{1,2})", 2))
Find out more about the powerful TOROW and TOCOL functions in our detailed article here. They will make sorting your data and organizing your information in Excel much easier.
Example 4: Extract Multiple Dates from Text
In scenarios where there are multiple dates in a single cell, you can use Excel’s REGEXEXTRACT function to extract each date into its own cell.
Step 1: Basic Extraction with REGEXEXTRACT
Use the ‘return_mode’ argument and select ‘1’, which means ‘All Matches’. This ensures that all instances of dates are extracted from the text.
=REGEXEXTRACT(B2, "(\d{4})-(\d{1,2})-(\d{1,2})", 1)
Step 2: Handling Multiple Columns with TOROW
To spill the extracted dates across a row instead of down a column, combine REGEXEXTRACT with the TOROW function.
=TOROW(REGEXEXTRACT(B2, "(\d{4})-(\d{1,2})-(\d{1,2})", 1))
Featured Course
Automate Excel: Power Query & Power Automate Recipes
Example 5: Extract Website URL from Text
Learn how to efficiently extract website URLs from text in an Excel table with the following step-by-step guide.
Step 1: Basic Extraction with REGEXEXTRACT
To extract all URLs from the text in column A into column B, use the REGEXEXTRACT function. The name of the Excel table is ‘Feedback’. Enter this formula in cell B2:
=REGEXEXTRACT([@Feedback], "(https?://[a-z0-9.-]+)|([a-z0-9.-]+.[a-z]{2, })" )Step 2: Handling Upper and Lower Case
Include URLs with upper case by using the ‘case_sensitivity’ argument. Select ‘1’ for a case-insensitive match:
=REGEXEXTRACT([@Feedback], "(https?://[a-z0-9.-]+)|([a-z0-9.-]+.[a-z]{2, })" , , 1)Step 3: Handling Multiple URLs in Text
To extract all URLs in a single cell, use the ‘return_mode’ argument set to ‘1’ for all matches. Combine the formula with the TEXTJOIN function to merge all results separated by a delimiter:
=TEXTJOIN(", ", TRUE, REGEXEXTRACT([@Feedback], "(https?://[a-z0-9.-]+)|([a-z0-9.-]+.[a-z]{2,})", 1, 1))
Example 6: Split a String (Combined Words)
Learn how to efficiently split names that are stuck together into proper first and last names. You can easily do this using Excel’s TEXTJOIN and REGEXEXTRACT functions.
Use the REGEXEXTRACT function to extract proper names from the combined words in column B. Enter this formula in cell C2:
=TEXTJOIN(" ", TRUE, REGEXEXTRACT(B2, "([A-Z][a-z]+)", 1))This formula extracts the proper names from the combined words, considering the capitalization of the first letter, and combines them into a single cell with the TEXTJOIN function, separating them with a space.
Example 7: Strip Leading Zeros
Learn how to efficiently strip leading zeros from product code numbers in Excel using the REGEXREPLACE and VALUE functions.
Step 1: Use REGEXREPLACE to Replace Leading Zeros
Use the REGEXREPLACE function to replace the leading zeros with nothing (”). Enter this formula in cell B2:
=REGEXREPLACE(A2:A8,"^0+(?!$)", "")Step 2: Convert to Number Format with VALUE
To change the format of the result from text to number, use the Excel VALUE function. It converts a text string into a number.
=VALUE(REGEXREPLACE(A2:A8, "^0+(?!$)", ""))
Example 8: Replace Special Characters
Learn how to efficiently remove special characters like #, @, or emojis from text in Excel using the REGEXREPLACE and IFERROR functions.
Use the REGEXREPLACE function to replace special characters with an empty string. Enter this formula in cell C2:
=IFERROR(REGEXREPLACE(B2:B11, "[^a-zA-Z0-9 &.]", ""), B2:B11)This formula removes all special characters, including emojis, from the text in column B. The IFERROR function ensures that if there are no special characters in the string, the original text remains unchanged.
Example 9: Insert a Character at a Specific Position
Learn how to efficiently insert a character at a specific position in a string, such as credit card numbers. We’ll do this using the REGEXREPLACE function in Excel.
Use the REGEXREPLACE function along with a regular expression pattern to insert a character at a specific position. Enter this formula in cell B2:
=REGEXREPLACE(A2, "(.{4})(?=.{4})", "$1-")This formula inserts a hyphen (-) after every fourth character in the string in cell A2, properly formatting credit card numbers for display.
How to Use Regex in XLOOKUP and XMATCH
In December 2024, Microsoft added regex support to XLOOKUP and XMATCH through a new match_mode option. Set the match_mode argument (the fifth argument in XLOOKUP, the third in XMATCH) to 3 and pass a regex pattern as the lookup_value.
This is available in Microsoft 365 for Windows, Mac, and the web. It is not available in Excel 2021 or earlier.
XLOOKUP with Regex: Fuzzy Country Match
Suppose your data lists the same country under different names: “USA”, “United States”, and “US.” You need to find the first matching row regardless of which variation appears.
=XLOOKUP("USA|United States|US", A2:A100, B2:B100, "Not found", 3)The | operator means OR. XLOOKUP checks each cell in A2:A100 and returns the value from column B for the first cell that matches any of the three variations.
XMATCH with Regex: Find a Product Code Position
To find the row position of the first product code that starts with three uppercase letters followed by two or more digits:
=XMATCH("^[A-Z]{3}\d{2,}", A2:A100, 3)This returns the relative position within the range. Combine it with INDEX if you need to return a value from a different column.
Regex Match Mode vs. Wildcard Match Mode
XLOOKUP and XMATCH already supported wildcards through match_mode 2. Here is how the two modes compare:
| Feature | Wildcard (match_mode 2) | Regex (match_mode 3) |
|---|---|---|
| Syntax | * (any characters), ? (one character) | Full PCRE2 regex patterns |
| Case sensitivity | Case-insensitive | Case-sensitive by default |
| OR logic | Not supported | Use | (e.g., “cat|dog”) |
| Anchors | Not supported | ^ (start), $ (end), \b (word boundary) |
| Character classes | Not supported | [0-9], [A-Z], \d, \w, \s |
Use wildcard mode for simple “starts with” or “contains” lookups. Use regex mode when you need OR logic, anchored patterns, character classes, or case-sensitive matching. To make a regex lookup case-insensitive, add (?i) at the start of your pattern:
=XLOOKUP("(?i)usa|united states", A2:A100, B2:B100, "Not found", 3)For the full XLOOKUP syntax and more examples beyond regex matching, see our complete XLOOKUP tutorial.
Regex Cheat Sheet: Common Patterns for Excel
All three Excel regex functions (REGEXEXTRACT, REGEXREPLACE, and REGEXTEST) use the PCRE2 flavor of regular expressions. This is the same syntax used by regex101.com (select “PCRE2” flavor) and most modern programming languages.
Below is a quick reference for the tokens and patterns you will use most often.
Basic Tokens
| Token | Meaning | Example Match |
|---|---|---|
| \d | Any digit (0-9) | “3” in “Order 3A” |
| \D | Any non-digit | “O” in “Order 3A” |
| \w | Any word character (letter, digit, underscore) | “O” in “Order 3A” |
| \W | Any non-word character | ” ” (space) in “Order 3A” |
| \s | Any whitespace (space, tab, newline) | ” ” in “Order 3A” |
| \S | Any non-whitespace | “O” in “Order 3A” |
| . | Any character except newline | “O” in “Order” |
Quantifiers
| Quantifier | Meaning | Example |
|---|---|---|
| + | One or more | \d+ matches “123” in “Order123” |
| * | Zero or more | ab*c matches “ac” and “abbc” |
| ? | Zero or one | colou?r matches “color” and “colour” |
| {n} | Exactly n times | \d{4} matches “2024” but not “24” |
| {n,m} | Between n and m times | \d{2,4} matches “24”, “123”, “2024” |
Anchors and Boundaries
| Symbol | Meaning | Example |
|---|---|---|
| ^ | Start of string | ^Order matches “Order123” but not “My Order” |
| $ | End of string | \.com$ matches “site.com” but not “site.com/page” |
| \b | Word boundary | \bcat\b matches “cat” but not “category” |
Groups and Alternation
| Syntax | Meaning | Example |
|---|---|---|
| (abc) | Capture group | Use with REGEXEXTRACT return_mode 2 to extract specific portions |
| a|b | OR (alternation) | cat|dog matches “cat” or “dog” |
| [abc] | Character class (any one of a, b, or c) | [aeiou] matches any vowel |
| [^abc] | Negated class (any character except a, b, c) | [^0-9] matches any non-digit |
| [A-Z] | Range (any uppercase letter) | [A-Za-z] matches any letter |
Ready-to-Use Patterns
These are common patterns you can paste directly into REGEXEXTRACT, REGEXREPLACE, or REGEXTEST.
| Use Case | Pattern |
|---|---|
| Email address | [A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,} |
| Phone (###-###-####) | \d{3}-\d{3}-\d{4} |
| Phone ((###) ###-####) | \(\d{3}\)\s?\d{3}-\d{4} |
| Date (YYYY-MM-DD) | \d{4}-\d{1,2}-\d{1,2} |
| Date (MM/DD/YYYY) | \d{1,2}/\d{1,2}/\d{4} |
| URL (http/https) | https?://[^\s]+ |
| IP address (basic) | \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3} |
| US zip code (5 digits) | ^\d{5}$ |
| US zip code (5+4) | ^\d{5}(-\d{4})?$ |
| Leading zeros | ^0+(?!$) |
| Non-alphanumeric characters | [^a-zA-Z0-9 ] |
| Digits only | ^\d+$ |
| Letters only | ^[A-Za-z]+$ |
To test any pattern before using it in Excel, go to regex101.com, select the PCRE2 flavor, paste your pattern, and enter sample text. The site highlights matches in real time and explains each token.
The downloadable workbook includes a printed version of this cheat sheet on a separate tab. Download the practice workbook and cheat sheet here.
Availability of Regex in Excel
REGEXEXTRACT, REGEXREPLACE, and REGEXTEST are now generally available in Excel 365 for Windows and Mac.
No Insider program required. Just make sure your Microsoft 365 is up to date.
If the functions are not showing up on your desktop, check that Excel is fully updated. They are also available in Excel for the web
Download the Workbook
Download the Regex Cheat Sheet
Master patterns and regular expressions with the one-page cheat sheet and practice files from the tutorial.
Featured Bundle
Power Pro Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
