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.

Practice

1. Which function would you use in Excel to replace all occurrences of the word apple with orange in a text string?

easy

A. REPLACE

B. SUBSTITUTE

C. CONCATENATE

D. LEFT

Solution

1. Step 1: Understand the function purpose

   SUBSTITUTE replaces specific text by matching the exact word or phrase in the string.

2. Step 2: Compare with REPLACE

   REPLACE changes text based on position, not matching text, so it is not suitable here.

3. Final Answer:

   SUBSTITUTE -> Option B

4. Quick Check:

   Replace by matching text = SUBSTITUTE [OK]

Hint: Use SUBSTITUTE to replace specific words by matching text [OK]

Common Mistakes:

• Using REPLACE when you need to match text
• Confusing SUBSTITUTE with CONCATENATE
• Trying to use LEFT to replace text

2. Which of the following is the correct syntax to replace the first 3 characters in cell A1 with XYZ using the REPLACE function?

easy

A. REPLACE("XYZ", 1, 3, A1)

B. REPLACE(A1, "XYZ", 1, 3)

C. REPLACE(A1, 3, 1, "XYZ")

D. REPLACE(A1, 1, 3, "XYZ")

Solution

1. Step 1: Recall REPLACE syntax

   REPLACE(text, start_num, num_chars, new_text) replaces characters starting at start_num for num_chars length.

2. Step 2: Match syntax to question

   To replace first 3 characters in A1 with "XYZ", start_num=1, num_chars=3, new_text="XYZ".

3. Final Answer:

   REPLACE(A1, 1, 3, "XYZ") -> Option D

4. Quick Check:

   REPLACE(text, 1, 3, "XYZ") correct syntax [OK]

Hint: REPLACE(text, start, length, new_text) replaces by position [OK]

Common Mistakes:

• Swapping start_num and num_chars
• Putting new_text in wrong position
• Using SUBSTITUTE syntax for REPLACE

3. What will be the result of the formula =SUBSTITUTE("banana", "a", "o", 2)?

medium

A. "bonana"

B. "banana"

C. "banona"

D. "bonono"

Solution

1. Step 1: Understand SUBSTITUTE with instance number

   SUBSTITUTE(text, old_text, new_text, instance_num) replaces only the specified occurrence.

2. Step 2: Apply to "banana" replacing 2nd "a" with "o"

   "banana" has 'a' at positions 2, 4, and 6. The 2nd 'a' is at position 4, replaced by 'o' gives "banona".

3. Final Answer:

   "banona" -> Option C

4. Quick Check:

   Replace 2nd 'a' with 'o' = "banona" [OK]

Hint: SUBSTITUTE with instance replaces only that occurrence [OK]

Common Mistakes:

• Replacing all 'a's instead of just the 2nd
• Confusing instance number with position
• Expecting REPLACE to work by matching text

4. The formula =REPLACE(A1, 2, 3, "XYZ") returns an error. Which is the most likely cause?

medium

A. The start position 2 is greater than the length of text in A1

B. Cell A1 is empty

C. The number of characters to replace is negative

D. The new text "XYZ" is too long

Solution

1. Step 1: Check REPLACE parameters

   REPLACE(text, start_num, num_chars, new_text) requires start_num within text length.

2. Step 2: Identify error cause

   If start_num (2) is greater than text length, REPLACE cannot start and returns error.

3. Final Answer:

   The start position 2 is greater than the length of text in A1 -> Option A

4. Quick Check:

   Start position must be within text length [OK]

Hint: Start position must be inside text length for REPLACE [OK]

Common Mistakes:

• Assuming empty cell causes error
• Thinking new text length matters
• Using negative number of characters

5. You have the text "Invoice12345" in cell A1. Which formula will replace the numeric part with "67890" regardless of how many digits the number has?

hard

A. REPLACE(A1, 8, LEN(A1)-7, "67890")

B. SUBSTITUTE(A1, "12345", "67890")

C. REPLACE(A1, 8, 5, "67890")

D. SUBSTITUTE(A1, MID(A1,8,5), "67890")

Solution

1. Step 1: Identify numeric part position and length

   The text "Invoice12345" has "Invoice" (7 chars) then numbers starting at position 8. Length of numbers is LEN(A1)-7.

2. Step 2: Use REPLACE with dynamic length

   REPLACE(A1, 8, LEN(A1)-7, "67890") replaces from position 8 for all digits, no matter how many.

3. Final Answer:

   REPLACE(A1, 8, LEN(A1)-7, "67890") -> Option A

4. Quick Check:

   Dynamic length replace = REPLACE with LEN() [OK]

Hint: Use LEN() to replace variable-length text with REPLACE [OK]

Common Mistakes:

• Hardcoding length instead of using LEN()
• Using SUBSTITUTE which needs exact text match
• Using MID inside SUBSTITUTE incorrectly