Google Sheets Function: REGEXEXTRACT

The REGEXEXTRACT function extracts one or more parts of a text using regular expressions.

Usage:

=REGEXEXTRACT(text, regular_expression)

If you are unfamiliar with what a regular expression is or how to write one, start by thoroughly reading the complete tutorial accompanying the REGEXMATCH function before proceeding (to save yourself some headaches when reading some of the regex on this page).

Extracting the First Word Found

The REGEXEXTRACT function here returns the first word found among the two possibilities ("fun" or "terrifying"):

=REGEXEXTRACT(A2,"fun|terrifying")

google sheets function regexextract word

Extracting a Reference

This time, the REGEXEXTRACT function returns the part of the reference number matching the expression "[A-Z]\d{2}" (i.e., an uppercase letter followed by 2 digits):

=REGEXEXTRACT(A2,"[A-Z]\d{2}")

google sheets function regexextract extract reference

Extracting a Number

The regex "\d*" corresponds to "all digits in unlimited quantity," meaning the function will extract all digits until encountering a non-digit character:

=REGEXEXTRACT(A2,"\d*")

google sheets function regexextract numbers

Extracting at the End

To extract the digits at the end of the text this time, add a "$" to the end of the regex:

=REGEXEXTRACT(A2,"\d*$")

google sheets function regexextract numbers end

Extracting Multiple Parts

To extract the different groups of numbers, add parentheses around each part of the text to extract:

=REGEXEXTRACT(A2,"(\d*)-?(\d*)-?(\d*)-?(\d*)")

google sheets function regexextract extract multiple strings

Extracting in the Middle

For example, to extract the second group of numbers, the regex is as follows:

=REGEXEXTRACT(A2,"(?:(\d*)-?){2}")

The addition of "?:", after the first parenthesis indicates it is non-capturing (so it won't return any value). There is then only one pair of parentheses left (thus only one value returned) and a quantifier of 2:

google sheets function regexextract non capturing parentheses