REGEXMATCH and REGEXEXTRACT in Google Sheets - Step-by-Step Guide

Introduction

These two functions help you find and pull out text patterns in your spreadsheet. REGEXMATCH checks if a pattern exists in a cell, and REGEXEXTRACT pulls out the matching part. They solve the problem of searching and extracting text without manual work.

When you want to check if an email address in a cell is valid by looking for the '@' symbol.

When you need to pull out the area code from a phone number in a list.

When you want to find if a product code contains certain letters or numbers.

When you want to extract the date from a text string like 'Order on 2024-06-15'.

When you want to filter rows that contain a specific word pattern.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula is entered for evaluation

💡 Start with =REGEXMATCH(text, pattern) or =REGEXEXTRACT(text, pattern)

Step 3: In the formula, replace 'text' with the cell reference containing your text

- formula bar

The formula points to your data

Step 4: Replace 'pattern' with the text pattern you want to find, enclosed in double quotes

- formula bar

The formula knows what pattern to look for

Step 5: Press Enter

- formula bar

The cell shows TRUE or FALSE for REGEXMATCH, or the extracted text for REGEXEXTRACT

Step 6: Drag the fill handle down to apply the formula to other cells

- bottom right corner of the formula cell

The formula copies and adjusts for each row

Before vs After

Before

Cell A2 contains 'Call me at 555-1234', no formula in B2

After

Cell B2 contains =REGEXEXTRACT(A2, "\d{3}-\d{4}") and shows '555-1234'

Settings Reference

text

📍 first argument in formula

The text to search or extract from

Default: none

pattern

📍 second argument in formula

The pattern to find in the text

Default: none

Common Mistakes

Not putting the pattern inside double quotes

Google Sheets treats unquoted text as a cell or name, causing errors

Always enclose the pattern in double quotes, like "\d+"

Using REGEXEXTRACT when the pattern does not exist in the text

The formula returns an error if no match is found

Use REGEXMATCH first to check if the pattern exists before extracting

Summary

REGEXMATCH checks if a text contains a pattern and returns TRUE or FALSE.

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

Always put your pattern inside double quotes and test with REGEXMATCH before extracting.