Grab the practice workbook with our Regex Cheat Sheet 👉 HERE and follow along:

What is Regex in Excel and How Can It Help You?

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!

REGEXEXTRACT Function 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.

REGEXREPLACE Function 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.

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.

REGEXEXTRACT combination with TEXTJOIN function

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.

REGEXEXTRACT combination with TEXTJOIN and IFERROR functions

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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, "(([^)]+))")
REGEXEXTRACT text inside parentheses

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)
REGEXEXTRACT string in parentheses

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))
Regex Excel REGEXEXTRACT and TOROW functions

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))
Regex Excel REGEXEXTRACT and TOROW functions

Featured Course

Automate With Power Query – Recipes to solve business data challenges

Do you want to become more confident using Power Query and automate your entire data workflow? Join this course and learn from real-world scenarios.
Learn More

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))
Regex Excel REGEXEXTRACT and TEXTJOIN functions

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+(?!$)", ""))
Regex Excel REGEXREPLACE with VALUE function

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.

Regex Excel REGEXREPLACE

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.

Regex Excel REGEXREPLACE

Availability of Regex in Excel

The REGEXEXTRACT and REGEXEXTRACT functions in Excel are currently available in Microsoft 365 to Office Insider Beta Channel users running:

  • Windows: Version 2406 (Build 17715.20000) or later
  • Mac: Version 16.86 (Build 24051422) or later

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to use the new Excel Regex functions with hands-on examples. Download the workbook with the cheat sheet here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.