SUBSTITUTE and REPLACE in Excel - 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 correcting specific words and replacing outdated codes in the comments.

📊 Data: You have a list of customer feedback comments. Some comments contain the word 'delievery' which is a typo and should be corrected to 'delivery'. Also, some comments include old product codes like 'PRD123' that need to be replaced with new codes like 'PRD789'.

🎯 Deliverable: Create a cleaned version of the comments where the typos are fixed and old product codes are replaced with new ones using SUBSTITUTE and REPLACE formulas.

Sample Data

Comment ID	Customer Comment
1	Fast delievery and good packaging.
2	Product PRD123 was damaged on arrival.
3	Love the quick delievery service.
4	PRD123 is my favorite product.
5	Great quality but slow delievery.
6	Received PRD123 late but customer support helped.

1

Step 1: Create a new column named 'Corrected Comment' next to the 'Customer Comment' column to hold the cleaned comments.

Expected Result

A new column is added for cleaned comments.

2

Step 2: Use the SUBSTITUTE function to fix the typo 'delievery' by replacing it with 'delivery' in the comments.

=SUBSTITUTE(B2, "delievery", "delivery")

Expected Result

For Comment ID 1, the result is 'Fast delivery and good packaging.'

3

Step 3: Use the SUBSTITUTE function again to replace the old product code 'PRD123' with the new code 'PRD789' in the corrected comments from step 2.

=SUBSTITUTE(C2, "PRD123", "PRD789")

Expected Result

For Comment ID 2, the result is 'Product PRD789 was damaged on arrival.'

4

Step 4: Combine the two SUBSTITUTE functions into one formula to clean all comments in one step.

=SUBSTITUTE(SUBSTITUTE(B2, "delievery", "delivery"), "PRD123", "PRD789")

Expected Result

For Comment ID 3, the result is 'Love the quick delivery service.' and for Comment ID 4, 'PRD789 is my favorite product.'

5

Step 5: Use the REPLACE function to manually replace the old product code 'PRD123' with 'PRD789' in the comment for Comment ID 2, assuming you know the position of 'PRD123' starts at character 9 and is 6 characters long.

=REPLACE(B2, 9, 6, "PRD789")

Expected Result

The comment becomes 'Product PRD789 was damaged on arrival.'

6

Step 6: Apply the combined SUBSTITUTE formula from step 4 to all rows in the 'Corrected Comment' column to clean all comments.

Fill down the formula: =SUBSTITUTE(SUBSTITUTE(B2, "delievery", "delivery"), "PRD123", "PRD789")

Expected Result

All comments have 'delievery' corrected to 'delivery' and 'PRD123' replaced with 'PRD789'.

Final Result

Comment ID | Corrected Comment
-----------|---------------------------------------------
1          | Fast delivery and good packaging.
2          | Product PRD789 was damaged on arrival.
3          | Love the quick delivery service.
4          | PRD789 is my favorite product.
5          | Great quality but slow delivery.
6          | Received PRD789 late but customer support helped.

✓The SUBSTITUTE function is great for replacing specific text anywhere in a string.

✓You can nest SUBSTITUTE functions to fix multiple issues in one formula.

✓The REPLACE function works well when you know the exact position and length of the text to replace.

✓Cleaning data with these functions helps improve data quality for analysis.

Bonus Challenge

Create a formula that replaces only the second occurrence of the word 'delivery' (corrected typo) with 'shipment' in the comments, if it appears twice.

Show Hint

Use a combination of SUBSTITUTE with the instance_num argument to target the second occurrence.