REGEXMATCH and REGEXEXTRACT in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of REGEXMATCH with simple pattern

What is the output of the formula =REGEXMATCH("apple pie", "pie")?

Google Sheets

=REGEXMATCH("apple pie", "pie")

A TRUE

B #ERROR!

C FALSE

D apple pie

💡 Hint

REGEXMATCH returns TRUE if the pattern is found anywhere in the text.

The text "apple pie" contains the substring "pie". REGEXMATCH returns TRUE when the pattern matches any part of the text.

📊 Formula Result

intermediate

Extracting a number with REGEXEXTRACT

What is the result of =REGEXEXTRACT("Order #12345", "\d+")?

Google Sheets

=REGEXEXTRACT("Order #12345", "\d+")

A 12345

B #12345

C Order

D #ERROR!

💡 Hint

The pattern \d+ matches one or more digits.

The formula extracts the first sequence of digits from the text. Here, it finds "12345".

❓ Function Choice

advanced

Choose the correct formula to check if text starts with 'cat'

Which formula correctly returns TRUE if cell A1 starts with the word "cat"?

A =REGEXEXTRACT(A1, "cat$")

B =REGEXMATCH(A1, "^cat")

C =REGEXMATCH(A1, "cat$")

D =REGEXEXTRACT(A1, "^cat")

💡 Hint

The caret ^ means the start of the text in regex.

REGEXMATCH with pattern "^cat" returns TRUE if the text starts with "cat". The other options either check the end or use REGEXEXTRACT incorrectly.

🎯 Scenario

advanced

Extract domain name from email address

You have email addresses in column A like "[e-mail]". Which formula extracts only the domain name (example.com)?

A =REGEXMATCH(A1, "^(.+)@")

B =REGEXMATCH(A1, "@(.+)$")

C =REGEXEXTRACT(A1, "@(.+)$")

D =REGEXEXTRACT(A1, "^(.+)@")

💡 Hint

Use REGEXEXTRACT to get the part after @.

REGEXEXTRACT with pattern "@(.+)$" extracts everything after the @ symbol, which is the domain name. REGEXMATCH returns TRUE/FALSE, so options B and D are incorrect. Option C extracts the username, not the domain.

❓ data_analysis

expert

Count how many cells in range B1:B10 contain a 3-digit number

Which formula correctly counts cells in B1:B10 that contain any 3-digit number anywhere in the text?

A =COUNTIF(B1:B10, "*\d{3}*")

B =SUM(REGEXEXTRACT(B1:B10, "\d{3}"))

C =COUNTIF(B1:B10, "*###*")

D =SUMPRODUCT(REGEXMATCH(B1:B10, "\d{3}"))

💡 Hint

REGEXMATCH returns TRUE/FALSE; SUMPRODUCT can sum TRUE as 1.

COUNTIF does not support regex patterns like \d{3}, so options B and C fail. Option D uses REGEXMATCH to check each cell for a 3-digit number and sums TRUE values as 1, giving the count. Option D uses ### which is not regex. Option D tries to sum extracted text, which causes an error.

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