REGEXEXTRACT function

REGEXEXTRACT(Text, Regex, CapturingGroup?, CaseInsensitive?, MultipleLines?) REGEXEXTRACT(Text; Regex; CapturingGroup?; CaseInsensitive?; MultipleLines?)

Text

Text or { Text }

The text string the returned text is extracted from.

Regex

Text or { Text }

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

Number or { Number } (optional)

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

Logical or { Logical } (optional)

Whether the regular expression should ignore case. If omitted, FALSE is assumed.

MultipleLines

Logical or { Logical } (optional)

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

Text or { Text }

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":

REGEXEXTRACT("This is a test!!!", "(This) (is) (a) (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):

REGEXEXTRACT("This is a test!!!", "(This) (is) (a) (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" }:

REGEXEXTRACT("This is a test!!!", "(This) (is) (a) (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

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".

REGEXEXTRACT("This is a test!!!", "(This) (is) (a) (test)", 4)REGEXEXTRACT("This is a test!!!"; "(This) (is) (a) (test)"; 4)

Returns the text captured by the fourth capturing group, which is "test".

REGEXEXTRACT("This is a test!!!", "(This) (is) (a) (test)", { 2, 4 })REGEXEXTRACT("This is a test!!!"; "(This) (is) (a) (test)"; { 2; 4 })

Returns the text captured by the second and fourth capturing groups as an array, which is { "is", "test" }{ "is"; "test" }.

REGEXEXTRACTALL("This is a test!!!", "(This) (is) (a) (test)")REGEXEXTRACTALL("This is a test!!!"; "(This) (is) (a) (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.

REGEXEXTRACT("email: support@calcapp.net.", "[^@\s]+@.+\.[^\.$]{2,}")REGEXEXTRACT("email: support@calcapp.net."; "[^@\s]+@.+\.[^\.$]{2,}")

Returns the email address found in the text string, "support@calcapp.net".