REGEXEXTRACT function in Google Sheets

REGEXEXTRACT pulls out the part of a text that matches a pattern.

REGEXEXTRACT takes a piece out of a text by describing what the piece looks like, not where it sits. "The four digits", "whatever follows the @", "the first word". When the layout of the data varies from row to row, that is far easier than counting positions.

Syntax

=REGEXEXTRACT(text, regular_expression)

Argument	What it is
`text`	The text to search.
`regular_expression`	The pattern. If it contains brackets, only the part inside the brackets is returned.

Examples

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=REGEXEXTRACT(C2,"\d{4}")
Result: 1234
The first run of exactly four digits in PB-10-1234.
=REGEXEXTRACT(C2,"^[A-Z]+")
Result: PB
The capital letters at the start.
=REGEXEXTRACT(D5,"\d+")
Result: 12
The number in "12 kg". The result is text.
=VALUE(REGEXEXTRACT(D5,"\d+"))
Result: 12
VALUE turns it into a real number.
=REGEXEXTRACT(B2,"@(.+)")
Result: example.com
With brackets, only the bracketed part comes back: everything after the @.
=REGEXEXTRACT(B2,"(.+)@(.+)")
Result:
asha.verma example.com
Two pairs of brackets return two pieces, in two cells.
=REGEXEXTRACT(A3,"\d+")
Result: #N/A
No digits in RAVI KUMAR, so nothing matches.
=REGEXEXTRACT(A3,"\w+$")
Result: KUMAR
The last word: letters up to the end of the text.

How it works

The function finds the first place where the pattern matches and returns the matching text. If nothing matches, the result is #N/A.

The patterns are the same as for REGEXMATCH. That page has the table of building blocks.

Brackets choose what comes back

Without brackets, the whole match is returned. With brackets, called a capturing group, only what is inside them is returned. This lets you use surrounding text as a signpost without including it.

"Rs \d+" on "Total Rs 450" returns "Rs 450".
"Rs (\d+)" returns "450".

Several pairs of brackets return several pieces, spilled across cells, as the sixth example shows. That makes REGEXEXTRACT a splitter with more control than SPLIT.

If you need brackets only for grouping and do not want them to capture, begin the group with ?:, as in (?:Mr|Ms) (\w+).

Greedy and lazy

A + or * grabs as much as it can. On "a(b)c(d)", the pattern $(.+)$ returns "b)c(d", from the first opening bracket to the last closing one. Add a question mark to make it stop at the first opportunity: $(.+?)$ returns "b".

Useful patterns

To get	Pattern
the first number	`\d+`
a number with decimals	`\d+\.?\d*`
the first word	`^\w+`
the last word	`\w+$`
text before a dash	`^([^-]+)`
text after the last slash	`[^/]+$`
the domain of an email	`@(.+)`
text inside brackets	`$(.+?)$`
a six-digit PIN code	`\b\d{6}\b`

The result is text

Even when digits are extracted, the result is text. Wrap it in VALUE, as in the fourth example, before using it in sums. The good side of this: leading zeros survive. Extracting "0042" keeps it as "0042", where SPLIT would turn it into 42.

Whole columns

Inside ARRAYFORMULA, it works down a column: =ARRAYFORMULA(IF(A2:A="", "", REGEXEXTRACT(A2:A, "\d+"))).

When there may be no match

One row without a match shows #N/A. Wrap in IFERROR to leave the cell blank or show a note: =IFERROR(REGEXEXTRACT(A2, "\d+"), "").

Only the first match

REGEXEXTRACT returns the first match only. To collect every match in a cell, a common method is to replace everything that is not a match with a separator using REGEXREPLACE, and then SPLIT the result.

Common mistakes

Numbers as input. The function needs text: use TO_TEXT(A2) or A2&"".
Brackets added for grouping that change what is returned.
A greedy .+ taking too much.
Forgetting that the result is text.
Case: patterns are case sensitive unless they start with (?i).

In Excel

Excel 2021 and earlier have nothing equivalent. Recent Microsoft 365 has REGEXEXTRACT of its own, with extra arguments. Elsewhere in Excel the job is done with MID and FIND, or with Flash Fill.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.