SUBSTITUTE and REPLACE in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a customer service analyst at an online retail company.

📋 Request: Your manager wants you to clean and update the customer feedback data by fixing typos and replacing outdated product codes.

📊 Data: You have a list of customer comments with some misspelled words and product codes that need updating.

🎯 Deliverable: A cleaned-up column of feedback with corrected words and updated product codes using SUBSTITUTE and REPLACE formulas.

Sample Data

Customer ID	Feedback
101	Love the prodcut ABC123, very useful!
102	Product ABC123 is great but delivery was slow.
103	Received prodcut XYZ789, but it was damaged.
104	prodcut ABC123 needs better packaging.
105	Happy with product XYZ789, fast shipping!

1

Step 1: Use SUBSTITUTE to fix the misspelled word 'prodcut' to 'product' in the Feedback column.

=SUBSTITUTE(B2, "prodcut", "product")

Expected Result

Love the product ABC123, very useful!

2

Step 2: Apply the SUBSTITUTE formula to all rows in a new column to correct all misspellings.

Copy the formula =SUBSTITUTE(B2, "prodcut", "product") down from row 2 to row 6.

Expected Result

All 'prodcut' words corrected to 'product' in the new column.

3

Step 3: Use REPLACE to update the old product code 'ABC123' to 'DEF456' in the corrected feedback text.

=REPLACE(C2, FIND("ABC123", C2), 6, "DEF456")

Expected Result

Love the product DEF456, very useful!

4

Step 4: Apply the REPLACE formula to all rows where 'ABC123' appears to update the product code.

Copy the formula =IF(ISNUMBER(FIND("ABC123", C2)), REPLACE(C2, FIND("ABC123", C2), 6, "DEF456"), C2) down the column.

Expected Result

All 'ABC123' codes replaced with 'DEF456' in the new column; other rows remain unchanged.

5

Step 5: Combine SUBSTITUTE and REPLACE in one formula to clean feedback and update product codes in one step.

=IF(ISNUMBER(FIND("ABC123", SUBSTITUTE(B2, "prodcut", "product"))), REPLACE(SUBSTITUTE(B2, "prodcut", "product"), FIND("ABC123", SUBSTITUTE(B2, "prodcut", "product")), 6, "DEF456"), SUBSTITUTE(B2, "prodcut", "product"))

Expected Result

Cleaned feedback with 'prodcut' fixed and 'ABC123' replaced by 'DEF456' in one formula.

Final Result

Customer ID | Cleaned Feedback
---------------------------------------------
101         | Love the product DEF456, very useful!
102         | Product DEF456 is great but delivery was slow.
103         | Received product XYZ789, but it was damaged.
104         | product DEF456 needs better packaging.
105         | Happy with product XYZ789, fast shipping!

✓All misspelled words 'prodcut' were corrected to 'product'.

✓Old product code 'ABC123' was updated to 'DEF456' wherever it appeared.

✓Feedback text is now clean and consistent for better analysis.

Bonus Challenge

Create a formula that also replaces 'XYZ789' with 'LMN321' in the cleaned feedback, combining all replacements in one formula.

Show Hint

Use nested SUBSTITUTE functions to replace multiple different text strings in one formula.