REGEXMATCH and REGEXEXTRACT in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a customer support analyst at an online store.

📋 Request: Your manager wants you to identify orders that include a specific product code pattern and extract the product code from the order descriptions.

📊 Data: You have a list of order descriptions. Each description may contain a product code in the format 'PROD' followed by 3 digits, like PROD123.

🎯 Deliverable: Create a new column that shows TRUE if the order description contains a product code and another column that extracts the product code from the description.

Sample Data

Order ID	Order Description
1001	Customer bought PROD123 and PROD456
1002	Order includes item PROD789
1003	No product code here
1004	Special offer on PROD234
1005	Multiple items: PROD111, PROD222
1006	Just a regular order

1

Step 1: Create a new column named 'Has Product Code' next to the 'Order Description' column.

In cell C2, enter the formula: =REGEXMATCH(B2, "PROD\\d{3}")

Expected Result

TRUE for rows 2, 3, 5, 6, and FALSE for rows 4 and 7 based on sample data.

2

Step 2: Create another column named 'Extracted Product Code' next to 'Has Product Code'.

In cell D2, enter the formula: =IFERROR(REGEXEXTRACT(B2, "PROD\\d{3}"),"")

Expected Result

Extracts 'PROD123' for row 2, 'PROD789' for row 3, 'PROD234' for row 4, 'PROD111' for row 5, and blank for rows without a product code.

3

Step 3: Copy the formulas in C2 and D2 down to all rows of your data.

Drag the fill handle from C2 and D2 down to row 7.

Expected Result

All rows show TRUE/FALSE in 'Has Product Code' and the first product code found in 'Extracted Product Code' or blank if none.

Final Result

Order ID | Order Description                 | Has Product Code | Extracted Product Code
-----------------------------------------------------------------------------------------
1001     | Customer bought PROD123 and PROD456 | TRUE            | PROD123
1002     | Order includes item PROD789         | TRUE            | PROD789
1003     | No product code here                 | FALSE           | 
1004     | Special offer on PROD234             | TRUE            | PROD234
1005     | Multiple items: PROD111, PROD222    | TRUE            | PROD111
1006     | Just a regular order                 | FALSE           | 

✓Rows with product codes show TRUE in 'Has Product Code'.

✓The first product code in each description is extracted correctly.

✓Rows without product codes show FALSE and blank extraction.

Bonus Challenge

Modify the extraction formula to extract all product codes from each order description separated by commas.

Show Hint

Use REGEXEXTRACT combined with TEXTJOIN and ARRAYFORMULA to capture multiple matches.