ID EN
…
#Dynamic array

REGEXEXTRACT

Excel Functions

Extract text with regex pattern

Syntax

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

Arguments

Parameter Description
text The text value to extract from.
pattern The pattern to extract.
return_mode [optional] 0 = first match, 1 = all matches, 2 = capture groups.
case_sensitivity [optional] 0 = Case sensitive, 1= Case-insensitive. Default is 0.

Return Value

Text matching pattern

Details

The REGEXEXTRACT function extracts text matching a specific regex pattern from a given text string. 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. With REGEXEXTRACT, you can easily extract numbers, dates, times, email addresses, and other text with a recognizable structure. REGEXEXTRACT not only saves time but also reduces errors created by complicated workarounds. The REGEXEXTRACT function provides a way to extract text values using regular expressions. To use REGEXEXTRACT, provide the text string to extract from and a regex pattern. For example, to extract a number from a text string, you can use REGEXEXTRAC

Examples

Example - Extracting numbers

The REGEXEXTRACT function provides a way to extract text values using regular expressions. To use REGEXEXTRACT, provide the text string to extract fro

EXCEL 
=REGEXEXTRACT("10 apples","[0-9]+") // returns "10"
Example - Extracting numbers

You can see how this works in the worksheet below, where the formula in cell D5, copied down, is:

EXCEL 
=REGEXEXTRACT(B5,"[0-9]+")
Example - Extracting phone numbers

Expanding on the example above, the worksheet below shows how to match and extract phone numbers in the format xxx-xxx-xxxx (i.e., a number like 888-1

EXCEL 
=REGEXEXTRACT(B5,"\d{3}-\d{3}-\d{4}")
Example - Extracting email addresses

Another classic use of regex is matching and extracting email addresses. In the worksheet below, the formula in cell D5, copied down, is:

EXCEL 
=REGEXEXTRACT(B5, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")
Example - Extracting dates

Another classic problem in Excel is how to extract a date from a text string. Traditionally, you might use a formula based on the SEARCH function and

EXCEL 
=MID(A1,SEARCH("??/??/??",A1),8)+0
Example - Extracting dates

However, SEARCH only supports Excel's very primitive wildcards, so the formula above is error-prone. With REGEXEXTRACT, we can use a more robust formu

EXCEL 
=REGEXEXTRACT(A1,"\b\d{1,2}/\d{1,2}/\d{2,4}\b")+0

See Also

REGEXTEST REGEXREPLACE