REGEXEXTRACT Function in Excel 2007, 2010, 2013, 2016, 2019, 2021 & 2024

The REGEXEXTRACT function is officially available only on Excel 365, but thanks to Modern Functions Update (MFU) you can now run it on your Excel 2007, 2010, 2013, 2016, 2019, 2021 and 2024 versions. It works exactly as its original Microsoft counterpart.

---

Description

The REGEXEXTRACT function extracts text from a string based on a provided regular expression pattern. For the advanced Excel user, this function is a major upgrade. Instead of working out complex formulas based on functions like LEFT, RIGHT, FIND, MID, etc., REGEXEXTRACT can target data very precisely with a single regex pattern.

You can extract the first match, all matches, or capturing groups from the first match.

This is particularly useful for:

• Extracting numbers: Pull digits from alphanumeric strings 
• Parsing emails: Extract email addresses from text 
• Extracting phone numbers: Pull phone numbers in various formats 
• Extracting URLs: Get website addresses from text 
• Extracting dates: Pull dates in various formats 
• Data cleaning: Extract specific patterns from messy data 

---

Syntax

=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

Argument	Description
text	Required. The text or reference to a cell containing the text you want to extract from.
pattern	Required. The regular expression (“regex”) that describes the text pattern you want to extract.
[return_mode]	Optional. A number specifying which strings to extract. Default is 0: • 0 – Returns the first string that matches the pattern • 1 – Returns all strings that match the pattern as an array • 2 – Returns capturing groups from the first match as an array
[case_sensitivity]	Optional. Determines if the match is case-sensitive. Default is 0: • 0 – Case-sensitive match • 1 – Case-insensitive match

---

Basic Tokens Reference

Token	Matches
[0-9]	Any numeric digit
[a-z]	Any lowercase letter
[A-Z]	Any uppercase letter
.	Any single character
a	The literal character “a”
a*	Zero or more “a”
a+	One or more “a”
\d	Any digit (same as [0-9])
\s	Any whitespace character
\b	Word boundary
^	Start of a text string (anchor)
$	End of a text string (anchor)
|	OR logic between patterns

---

Examples

Basic Usage – Extracting Numbers

To extract a number from a text string:

=REGEXEXTRACT("10 apples", "[0-9]+")

Returns: "10"

The pattern [0-9]+ means “match one or more characters, where each character is a digit from 0 to 9”. You can also use the more compact pattern \d+.

Converting to a Numeric Value

REGEXEXTRACT always returns text values. To convert to a number:

=VALUE(REGEXEXTRACT(A1, "\d+"))

Extracting Names Based on Capital Letters

With the pattern [A-Z][a-z]+:

=REGEXEXTRACT("DylanWilliams", "[A-Z][a-z]+")

Returns: "Dylan"

=REGEXEXTRACT("DylanWilliams", "[A-Z][a-z]+", 1)

Returns: "Dylan" and "Williams" (spilled across two cells).

Extracting Phone Numbers

To extract phone numbers in the format (XXX) XXX-XXXX:

=REGEXEXTRACT("Sonia Rees (378) 555-4195", "[0-9()]+ [0-9-]+", 1)

Returns the phone number (378) 555-4195.

For phone numbers in format XXX-XXX-XXXX:

=REGEXEXTRACT(B5, "\d{3}-\d{3}-\d{4}")

Extracting Email Addresses

To extract an email address:

=REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

Case-Insensitive Search

By default, REGEXEXTRACT is case-sensitive. To ignore case:

=REGEXEXTRACT(A2, "excel", , 1)

Return All Matches

To return all matches instead of just the first:

=REGEXEXTRACT(A2, "#\w+", 1)

This extracts all hashtags from a text string.

Extracting URLs

To extract URLs from text:

=REGEXEXTRACT(A3, "(https?://|www\.)\S+")

Handling “Not Found”

If no match is found, REGEXEXTRACT returns a #N/A error. To handle this:

=IFERROR(REGEXEXTRACT(A1, "\d+"), "No number found")

Capturing Groups

To extract specific parts of a match using capturing groups, set return_mode to 2:

=REGEXEXTRACT(B5, "(\d{4})-(\w+)-(\w+)", 2)

This returns each capturing group (the parts in parentheses) as separate values.

---

Practical Use Cases

Scenario	Formula
Extract first number	=REGEXEXTRACT(A1, "\d+")
Extract all numbers	=REGEXEXTRACT(A1, "\d+", 1)
Extract email address	=REGEXEXTRACT(A1, "[\w\.-]+@[\w\.-]+\.\w+")
Extract URL	=REGEXEXTRACT(A1, "(https?://|www\.)\S+")
Extract phone number	=REGEXEXTRACT(A1, "\(\d{3}\)\s\d{3}-\d{4}")
Extract hashtags	=REGEXEXTRACT(A1, "#\w+", 1)
Extract date (MM/DD/YYYY)	=REGEXEXTRACT(A1, "\b\d{1,2}/\d{1,2}/\d{2,4}\b")
Case-insensitive search	=REGEXEXTRACT(A1, "pattern", , 1)

---

Notes

• ✅ Fully tested – The implementation in Modern Functions Update works identically to Microsoft’s original function introduced in Microsoft 365.
• ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
• ℹ️ REGEXEXTRACT uses the PCRE2 flavor of regular expressions.
• ℹ️ REGEXEXTRACT always returns text values. Use VALUE to convert to numbers.
• ℹ️ REGEXEXTRACT is case-sensitive by default. Use case_sensitivity = 1 for case-insensitive matching.
• ℹ️ If no match is found, REGEXEXTRACT returns #N/A.
• ⚠️ When return_mode is set to 1 or 2, results spill across adjacent cells as a dynamic array.
• ℹ️ Capturing groups (parts of a regex pattern enclosed in parentheses (...)) allow you to return separate parts of a single match individually.

---

See Also

• REGEXTEST – Tests if text matches a regex pattern
• REGEXREPLACE – Replaces text that matches a regex pattern
• TEXTBEFORE – Extracts text before a delimiter
• TEXTAFTER – Extracts text after a delimiter
• TEXTSPLIT – Splits text into multiple values
• IFNA – Handles #N/A errors gracefully