REGEXMATCH and REGEXEXTRACT in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

A list of email addresses in column A. Column B will check if the email looks valid using REGEXMATCH. Column C will extract the domain part of the email using REGEXEXTRACT.

Cell	Value
A1	Email List
A2	[e-mail]
A3	[e-mail]
A4	[e-mail]
A5	dave@invalid
B1	Is Valid Email?
C1	Extract Domain

Formula Trace

REGEXMATCH(A2, "^[\w.+-]+@[\w.-]+\.[a-zA-Z]{2,}$")

Step 1: A2 value is "[e-mail]"

Step 2: Pattern "^[\w.+-]+@[\w.-]+\.[a-zA-Z]{2,}$"

Step 3: Does "[e-mail]" match the pattern?

Cell Reference Map

    A               B               C
1 | Email List    | Is Valid Email? | Extract Domain
2 | alice@ex...  |                |               
3 | bob123@wo... |                |               
4 | carol@hom... |                |               
5 | dave@inva... |                |               

The formula uses cell A2 to check if the email is valid with REGEXMATCH, and later will use A2 to extract domain with REGEXEXTRACT.

Result

    A               B               C
1 | Email List    | Is Valid Email? | Extract Domain
2 | alice@ex...  | TRUE           | example.com   
3 | bob123@wo... | TRUE           | work.net      
4 | carol@hom... | TRUE           | home.org      
5 | dave@inva... | FALSE          | #N/A         

Column B shows TRUE if the email matches the pattern (valid), FALSE if not. Column C extracts the domain part after '@' for valid emails, otherwise shows error.

Sheet Trace Quiz - 3 Questions

What does REGEXMATCH(A2, "^[\w.+-]+@[\w.-]+\.[a-zA-Z]{2,}$") check?

A If A2 is empty

B If A2 contains the word 'email'

C If A2 contains a valid email format

D If A2 contains only numbers

Key Result

REGEXMATCH checks if text matches a pattern; REGEXEXTRACT pulls matching text from a pattern group.

Practice

1. What does the formula =REGEXMATCH("apple123", "\d+") return in Google Sheets?

easy

A. TRUE

B. FALSE

C. apple123

D. Error

Solution

1. Step 1: Understand REGEXMATCH function

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

2. Step 2: Analyze the pattern "\d+" on "apple123"

   "\d+" means one or more digits. "apple123" contains digits "123", so it matches.

3. Final Answer:

   TRUE -> Option A

4. Quick Check:

   Digits found = TRUE [OK]

Hint: REGEXMATCH returns TRUE if pattern exists anywhere in text [OK]

Common Mistakes:

• Thinking REGEXMATCH extracts text instead of TRUE/FALSE
• Confusing \d+ with matching letters
• Expecting the whole text to match pattern

2. Which of these formulas correctly extracts the first sequence of digits from cell A1 using REGEXEXTRACT?

easy

A. =REGEXMATCH(A1, "\d+")

B. =REGEXEXTRACT(A1, "[a-zA-Z]+")

C. =REGEXEXTRACT(A1, "\w+")

D. =REGEXEXTRACT(A1, "\d+")

Solution

1. Step 1: Identify the goal - extract digits

   We want to extract digits, so the pattern should match digits: "\d+".

2. Step 2: Check each option

   =REGEXEXTRACT(A1, "\d+") uses REGEXEXTRACT with "\d+" which extracts digits correctly. Others extract letters or use REGEXMATCH which returns TRUE/FALSE.

3. Final Answer:

   =REGEXEXTRACT(A1, "\d+") -> Option D

4. Quick Check:

   Digits extraction = =REGEXEXTRACT(A1, "\d+") [OK]

Hint: Use \d+ inside REGEXEXTRACT to get digits [OK]

Common Mistakes:

• Using REGEXMATCH instead of REGEXEXTRACT to get text
• Using letter patterns when digits are needed
• Wrong pattern syntax like missing escape \

3. Given cell A1 contains "Order #12345: shipped", what is the result of =REGEXEXTRACT(A1, "#(\d+)")?

medium

A. Order

B. #12345

C. 12345

D. Error

Solution

1. Step 1: Understand the pattern "#(\d+)"

   The pattern looks for a '#' followed by digits. The parentheses capture only the digits.

2. Step 2: Apply REGEXEXTRACT on "Order #12345: shipped"

   The formula extracts the digits after '#', which is "12345".

3. Final Answer:

   12345 -> Option C

4. Quick Check:

   Captured digits after # = 12345 [OK]

Hint: Parentheses capture only the part you want extracted [OK]

Common Mistakes:

• Expecting the # symbol to be included in output
• Not using parentheses to capture group
• Confusing REGEXMATCH output with REGEXEXTRACT

4. Why does the formula =REGEXEXTRACT("abc123", "[0-9]") return only "1" instead of "123"? How to fix it?

medium

A. REGEXEXTRACT cannot extract digits; use REGEXMATCH instead

B. Pattern matches only one digit; use "[0-9]+" to match all digits

C. Formula syntax is wrong; remove quotes around pattern

D. The text "abc123" has no digits; change text input

Solution

1. Step 1: Analyze the pattern "[0-9]"

   This pattern matches exactly one digit character, so REGEXEXTRACT returns only the first digit "1".

2. Step 2: Fix pattern to match all digits

   Adding + quantifier as "[0-9]+" matches one or more digits, extracting "123".

3. Final Answer:

   Pattern matches only one digit; use "[0-9]+" to match all digits -> Option B

4. Quick Check:

   Use + to match multiple characters [OK]

Hint: Add + to match multiple characters, not just one [OK]

Common Mistakes:

• Using REGEXMATCH instead of REGEXEXTRACT to get text
• Removing quotes around pattern causing syntax error
• Assuming pattern matches all digits without +

5. You have a list of emails in column A. Which formula extracts the username (text before @) from cell A2, assuming usernames can have letters, digits, dots, and underscores?

hard

A. =REGEXEXTRACT(A2, "^[\w\.]+")

B. =REGEXEXTRACT(A2, "@.+")

C. =REGEXMATCH(A2, "^[\w\.]+@")

D. =REGEXEXTRACT(A2, "[a-z]+")

Solution

1. Step 1: Understand the username pattern

   Usernames include letters, digits, dots (.), and underscores (_). The pattern "[\w\.]" matches letters, digits, underscores (\w) and dots (\.).

2. Step 2: Use REGEXEXTRACT with pattern "^[\w\.]+"

   The ^ anchors to start of text, and + matches one or more allowed characters before @. This extracts the username correctly.

3. Final Answer:

   =REGEXEXTRACT(A2, "^[\w\.]+") -> Option A

4. Quick Check:

   Extract username before @ with ^ and character class [OK]

Hint: Use ^ to anchor start and [\w\.] to match username chars [OK]

Common Mistakes:

• Extracting text after @ instead of before
• Using REGEXMATCH which returns TRUE/FALSE, not text
• Not escaping dot or missing underscore in pattern