REGEXEXTRACT

Summary

The REGEXEXTRACT function extracts text from a string based on a specified regular expression. It supports extracting the first match, all matches, or capturing groups from the first match.

Syntax

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

Arguments

Argument	Description
text Required	The input text or a cell reference containing the text to extract from.
pattern Required	The regular expression defining the pattern to match.
return_mode	Specifies the extraction mode: 0 (Default): Returns the first match. 1: Returns all matches as an array. 2: Returns capturing groups from the first match as an array. Note: Capturing groups are regex subpatterns enclosed in parentheses (...), allowing extraction of specific match segments.
case_sensitivity	Controls case sensitivity: 0 (Default): Case-sensitive. 1: Case-insensitive.

Examples

=REGEXEXTRACT("10 apples, 12 oranges","[0-9]+")  // 10
=REGEXEXTRACT("10 apples, 12 oranges","[0-9]+", 1)  // [10, 12]
=REGEXEXTRACT("09/12/1998 from James","\d{1,2}/\d{1,2}/\d{2,4}\b") // 09/12/1998
=REGEXEXTRACT("Sonia (378) 555-4195","\d{3}-\d{4}") // 555-4195
=REGEXEXTRACT("O my Luve red rose", "O my (\w+) red (\w+)") // O my Luve red rose
=REGEXEXTRACT("O my Luve red rose", "O my (\w+) red (\w+)", 2)  //["Luve", "rose"]

The REGEXEXTRACT function aligns with Excel’s implementation. The legacy SJS.REGEXEXTRACT function is deprecated but remains backward-compatible. Key differences:

Feature	REGEXEXTRACT	SJS.REGEXEXTRACT
Argument Types	Uses numeric return_mode and case_sensitivity. `=REGEXEXTRACT("aa","A",1,1)`	Uses string modifier (e.g., "ig"). `=SJS.REGEXEXTRACT("aa","A","ig")`
Capturing Groups	Enabled when return_mode=2 `=REGEXEXTRACT("O my Luve is like a red, red rose","O my (\w+) is like a red, red (\w+)",2)`	Enabled by default `=SJS.REGEXEXTRACT("O my Luve is like a red, red rose","O my (\w+) is like a red, red (\w+)")`
Extract All Matches	Use return_mode=1 `=REGEXEXTRACT("O my Luve is like a red, red rose. O my Luve is like a red, red rose","O my (\w+) is like a red, red (\w+)",1)`	Requires `"g"` modifier `=SJS.REGEXEXTRACT("O my Luve is like a red, red rose. O my Luve is like a red, red rose","O my (\w+) is like a red, red (\w+)","g")`
Extract First Match	Default behavior `=REGEXEXTRACT("O my Luve is like a red, red rose. O my Luve is like a red, red rose","O my (\w+) is like a red, red (\w+)")`	Not supported
Multiline Handling	Requires regex syntax `=REGEXEXTRACT("aaa` `aaa","^a	\na",1)`
Non-String Inputs	Converts numbers `=REGEXEXTRACT(1234,"1")`	Returns `#VALUE!` for non-strings.