Pulling Out Patterns With REGEXEXTRACT

REGEXMATCH tells you whether a pattern is present. Often you want the matched text itself, like the numbers out of Order #4821 or the domain out of an email.

That is the job of REGEXEXTRACT. It finds the first part of the text that matches your pattern and returns that piece as the cell's value.

This is another Google Sheets function. It is one of the cleanest ways to pull structured pieces out of messy imported data.

REGEXEXTRACT(text, pattern) returns the first substring of text that matches pattern.

If nothing matches, it returns a #N/A error rather than blank, which is important to remember.

The formula below pulls the first run of digits out of A2. For Order 4821 it returns 4821.

REGEXEXTRACT always returns text, even when the matched characters are digits. So 4821 comes back as a text string, not a number you can add up.

To turn it into a real number, wrap it in VALUE. The formula below extracts the digits and converts them so you can do math with the result.

Patterns are not limited to digits. To grab a prefix of uppercase letters, use a class with a quantifier.

The formula below pulls the leading letters from a code like INV2024, returning INV. [A-Z]+ means one or more uppercase letters in a row.

Suppose A2 holds SKU-4821-RED and you want just the numeric middle. You can describe the position with surrounding context inside the pattern.

The formula below looks for digits that sit between two hyphens and returns 4821. The literal hyphens guide the engine to the right run of digits.

Notice the parentheses in the last formula: (\d+). Those are a capture group.

When your pattern contains a capture group, REGEXEXTRACT returns only what is inside the parentheses, not the whole match. That is why -(\d+)- returns 4821 and drops the hyphens.

Capture groups let you match surrounding context for accuracy while still extracting only the slice you care about.

From sam@coddykit.com you want the domain coddykit.com. Match an @, then capture everything after it up to the end.

The formula below returns the part of A2 that follows the at sign. (.+) captures one or more of any character, and $ ties it to the end of the string.

Anchors help you grab a piece from a known position. To capture the first word only, anchor to the start and stop at the first space.

The formula below captures the leading non-space characters of A2. For John Smith it returns John. [^ ]+ means one or more characters that are not a space.

Because a failed extract throws #N/A, dragging the formula down a column with mixed data can litter your sheet with errors.

Wrap the call in IFERROR to provide a clean fallback. The formula below returns the digits if found, or an empty string if there are none.

The pipe | means or. It lets one pattern match several alternatives. This is great when a code might start with different prefixes.

The formula below extracts whichever status word appears in A2: OPEN, CLOSED, or HOLD. The first one found is returned.

REGEXEXTRACT shines when applied down a whole column of imported data. Each row gets its matching piece pulled into a clean new column you can sort or analyze.

Imagine column A holds order strings like Order 4821 - shipped. The formula below, dragged down column B, extracts just the order number from every row, giving you a tidy numeric-looking column from messy source text.

Check what you know about extracting matches.

You learned that REGEXEXTRACT(text, pattern) returns the first matching substring in Google Sheets. Key takeaways:

• The result is always text; wrap with VALUE for numbers
• Capture groups in parentheses return only the inner slice
• No match gives #N/A, so guard with IFERROR
• | lets one pattern match several alternatives

Next you will go beyond reading and start rewriting text in place with REGEXREPLACE.