REGEXMATCH and REGEXEXTRACT in Google Sheets - Dashboard Guide

Dashboard Mode - REGEXMATCH and REGEXEXTRACT

Dashboard Goal

Find and extract specific patterns from a list of product codes to quickly identify valid codes and pull out key parts like region and product type.

Sample Data

Row	Product Code
2	US-1234-XL
3	EU-5678-M
4	AS-ABCD-L
5	US-9999-S
6	XX-0000-XX
7	EU-4321-XL
8	InvalidCode

Dashboard Components

• KPI Card: Valid Codes Count
  Formula: =SUMPRODUCT(--REGEXMATCH(B2:B8, "^[A-Z]{2}-\d{4}-[SML]{1,2}|XL$"))
  Shows how many product codes match the pattern: two uppercase letters, dash, four digits, dash, size code (S, M, L, XL).
• Table: Validity Check
  Formula in C2: =REGEXMATCH(B2, "^[A-Z]{2}-\d{4}-(S|M|L|XL)$")
  Drag down to C8.
  Shows TRUE if the product code matches the pattern, FALSE otherwise.
• Table: Extract Region
  Formula in D2: =REGEXEXTRACT(B2, "^[A-Z]{2}")
  Drag down to D8.
  Extracts the two-letter region code from the start.
• Table: Extract Product Type
  Formula in E2: =REGEXEXTRACT(B2, "(S|M|L|XL)$")
  Drag down to E8.
  Extracts the size code at the end.

Dashboard Layout

+-------------------------+------------------------------+
| Valid Codes Count (KPI) | Product Codes Table           |
|                         | +------------+---------+--------+---------+|
|                         | | Code       | Valid?  | Region | Product |
|                         | |            |         |        | Type    |
|                         | +------------+---------+--------+---------+|
|                         | | US-1234-XL | TRUE    | US     | XL      |
|                         | | EU-5678-M  | TRUE    | EU     | M       |
|                         | | AS-ABCD-L  | FALSE   | AS     | L       |
|                         | | US-9999-S  | TRUE    | US     | S       |
|                         | | XX-0000-XX | FALSE   | XX     |         |
|                         | | EU-4321-XL | TRUE    | EU     | XL      |
|                         | | InvalidCode| FALSE   |        |         |
+-------------------------+------------------------------+

Interactivity

Add a filter dropdown to select Region codes (e.g., US, EU, AS, XX). When a region is selected, the table updates to show only product codes from that region. The Valid Codes Count KPI updates to count only valid codes in the selected region.

Self Check

If you add a filter for Region = "EU", which rows remain visible in the table and what is the updated Valid Codes Count?

Answer: Rows with product codes "EU-5678-M" and "EU-4321-XL" remain. Valid Codes Count updates to 2.

Key Result

Dashboard shows how to identify valid product codes and extract region and product type using REGEXMATCH and REGEXEXTRACT.

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