REGEXEXTRACTALL 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.
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
An array with the extracted text.
Extracts the parts of a text string identified by a JavaScript regular expression and returns an array with the extracted text. REGEXEXTRACTALL("The combination is 204228", "\d+")REGEXEXTRACTALL("The combination is 204228"; "\d+") returns the array { "204228" }{ "204228" }. "\d+" means "one or more digits (numbers)," causing REGEXEXTRACTALL to only return "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.
For a complex example of how REGEXEXTRACTALL can be used with capturing groups and arrays, refer to the documentation for the array variant of the TEXTJOIN function.
Capturing groups
This function primarily exists to process regular expressions containing capturing groups, which 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 there are no capturing groups in the regular expression given to this function, an array containing a single element is returned, representing the entire text string which has been found. In that case, use REGEXEXTRACT instead, which returns this text string directly.
Consider this formula:
(test)")REGEXEXTRACTALL("This is a test!!!"; "(This) (is) (a)
(test)")
It returns the array { "This", "is", "a", "test" }{ "This"; "is"; "a"; "test" }, which contains the text captured by all capturing groups, with one element per capturing group.
Examples
Returns the array { "204228" }{ "204228" }. "\d+" means "one or more digits (numbers)," causing REGEXEXTRACTALL to return only "204228". As there are no capturing groups in the regular expression, a single element is part of the returned array, which represents the entire text string which has been found.
Returns "204228". "\d+" means "one or more digits (numbers)," which explains why only "204228" is returned. REGEXEXTRACT returns the entire text string which has been found, without wrapping it in an array. REGEXEXTRACT is preferable to REGEXEXTRACTALL when the regular expression contains no capturing groups.
Returns the array { "This", "is", "a", "test" }{ "This"; "is"; "a"; "test" }, which contains the text captured by all capturing groups, with one element per capturing group.