REGEX.EXTRACT

The REGEX.EXTRACT function returns the matches or capture groups in a match of a given regular expression in a source string.

REGEX.EXTRACT(source-string, regular-expression-string, match-occurrence, capture-group-occurence)

source-string: A string value to be matched against.

regular-expression-string: A string value representing a regular expression, used to match against source-string.

match-occurence: An optional integer indicating which match should be returned, in case there are multiple matches. Negative integers are accepted and allow accessing the matches backwards, so -1 is the last match, and so on. If 0 is given, an array of all matches will be returned. If omitted, the first match is returned.

capture-group-occurence: An optional integer indicating which capture group within a match should be returned, in case there are capture groups in the regular expression. Negative values are not accepted. If 0 is returned, an array of all capture groups will be returned. If omitted, the entire match is returned.

Notes

The regular-expression-string has to conform to the ICU standard.

Examples
Let A1 be "jake@appleseed.com john@appleseed.ca mike@appleseed.de". =REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})") returns "jake@appleseed.com". =REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", 2) returns "john@appleseed.ca". =REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", -1) returns "mike@appleseed.de". =REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", 1, 1) returns "jake". =REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", 2, 2) returns "appleseed.ca".

Examples

Let A1 be "jake@appleseed.com john@appleseed.ca mike@appleseed.de".

=REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})") returns "jake@appleseed.com".

=REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", 2) returns "john@appleseed.ca".

=REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", -1) returns "mike@appleseed.de".

=REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", 1, 1) returns "jake".

=REGEX.EXTRACT(A1, "([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})", 2, 2) returns "appleseed.ca".

See alsoREGEX

Helpful?

Formulas and Functions Help

REGEX.EXTRACT

Notes