REGEXEXTRACT function
Text
The text string the returned text is extracted from.
Regex
The JavaScript regular expression which finds the extracted text. This
regular expression may use capturing groups, which may be used in
conjunction with the capturingGroup parameter to extract
text from a specific group.
CapturingGroup
The one-based number of the capturing group whose text should be returned, or a blank value or zero if the entire text string which has been found should be returned. If omitted, a blank value is assumed.
CaseInsensitive
Whether the regular expression should ignore case. If omitted, FALSE is assumed.
MultipleLines
Whether the regular expression anchors ^ and
$ should be relative to a line of text or to the text
string in its entirety. If omitted, FALSE is assumed.
Returns
The extracted text.
Extracts the parts of a text string identified by a JavaScript regular expression and returns the extracted text. REGEXEXTRACT("The combination is 204228", "\d+")REGEXEXTRACT("The combination is 204228"; "\d+") returns "204228". "\d+" means "one or more digits (numbers)," causing REGEXEXTRACT to return only "204228".
Regular expressions are a powerful tool to search through and manipulate text, though they take time to master. For an introduction, refer to the blog post we wrote when Calcapp gained support for regular expressions.
Capturing groups
If not omitted, the third parameter should be set to a number specifying what capturing group should be returned. Capturing groups make it possible to extract specific parts of a text string. They are added to a regular expression by enclosing these parts in parentheses. If the third parameter is not specified (or is set to BLANK()), the entire text string that matched is returned.
This formula returns the text captured by the fourth capturing group, "test":
(test)", 4)REGEXEXTRACT("This is a test!!!"; "(This) (is) (a)
(test)"; 4)
If the third parameter is omitted, the entire match is returned, "This is a test" (without the trailing exclamation points):
(test)")REGEXEXTRACT("This is a test!!!"; "(This) (is) (a)
(test)")
The third parameter can be set to an array of numbers, in which case an array is returned containing the text captured by all capturing groups, with one element per capturing group, { "is", "test" }{ "is"; "test" }:
(test)", { 2, 4 })REGEXEXTRACT("This is a test!!!"; "(This) (is) (a)
(test)"; { 2; 4 })
Use REGEXEXTRACTALL instead to return an array containing the text captured by all capturing groups.
Examples
Returns "204228". "\d+" means "one or more digits (numbers)," causing REGEXEXTRACT to return only "204228".
Returns the text captured by the fourth capturing group, which is "test".
Returns the text captured by the second and fourth capturing groups as an array, which is { "is", "test" }{ "is"; "test" }.
Returns the array { "This", "is", "a", "test" }{ "This"; "is"; "a"; "test" }. REGEXEXTRACTALL returns an array containing the text captured by all capturing groups by default.
Returns the email address found in the text string, "support@calcapp.net".