REGEXMATCH and REGEXEXTRACT in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to check if cell A1 contains the word "cat".

Google Sheets

=REGEXMATCH(A1, [1])

Drag options to blanks, or click blank then click option'

A"cat"

B"dog"

C"bird"

D"fish"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put quotes around the pattern.

Using the wrong word in the pattern.

The formula =REGEXMATCH(A1, "cat") returns TRUE if the text in A1 contains the word "cat".

2fill in blank

medium

Complete the formula to extract the first number from cell B2.

Google Sheets

=REGEXEXTRACT(B2, [1])

Drag options to blanks, or click blank then click option'

A"[a-z]+"

B"\\d+"

C"\\s+"

D"[A-Z]+"

Attempts:

3 left

💡 Hint

Common Mistakes

Using letters instead of digits in the pattern.

Not escaping the backslash properly.

The pattern "\\d+" matches one or more digits, so =REGEXEXTRACT(B2, "\\d+") extracts the first number from the text.

3fill in blank

hard

Fix the error in the formula to extract the domain from an email in cell C3.

Google Sheets

=REGEXEXTRACT(C3, [1])

Drag options to blanks, or click blank then click option'

A"@(.+)"

B"@(.+)$"

C"@(.+)\\..+"

D"@(.+\\..+)"

Attempts:

3 left

💡 Hint

Common Mistakes

Not including the dot in the domain pattern.

Using end-of-string anchor incorrectly.

The pattern "@(.+\\..+)" correctly extracts the domain part after the '@' including the dot and domain extension.

4fill in blank

hard

Fill both blanks to create a formula that checks if cell D4 contains a 3-letter word starting with 'a'.

Google Sheets

=REGEXMATCH(D4, [1] & [2])

Drag options to blanks, or click blank then click option'

A"\\ba"

B"\\w"

C"\\b"

D"\\w{2}\\b"

Attempts:

3 left

💡 Hint

Common Mistakes

Not using word boundary \\b.

Using wrong quantifier for length.

The pattern "\\ba" means word boundary followed by 'a', and "\\w{2}\\b" means two word characters followed by word boundary. Together they form \\ba\\w{2}\\b which checks for a 3-letter word starting with 'a'.

5fill in blank

hard

Fill all three blanks to extract the area code (3 digits) from a phone number in cell E5 formatted like [phone].

Google Sheets

=REGEXEXTRACT(E5, [1] & [2] & [3])

Drag options to blanks, or click blank then click option'

A"\\("

B"(\\d{3})"

C"\\)"

D"\\s"

Attempts:

3 left

💡 Hint

Common Mistakes

Not escaping parentheses.

Using wrong number of digits in the pattern.

The pattern "\\(" matches the opening parenthesis, "(\\d{3})" matches and captures exactly 3 digits (the area code), and "\\)" matches the closing parenthesis. Together they extract the area code from the phone number.

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