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.