SUBSTITUTE and REPLACE in Excel - Cell-by-Cell Formula Trace

Sample Data

Column A has text strings. B1 and B2 have words for substitution. B3 and B4 have numbers and text for replacement.

Cell	Value
A1	apple banana apple
A2	1234567890
B1	apple
B2	orange
B3	345
B4	XYZ

Formula Trace

=SUBSTITUTE(A1, B1, B2) & " | " & REPLACE(A2, 4, 3, B4)

Step 1: SUBSTITUTE("apple banana apple", "apple", "orange")

Step 2: REPLACE("1234567890", 4, 3, "XYZ")

Step 3: "orange banana orange" & " | " & "123XYZ7890"

Cell Reference Map

    A           B       
1 |apple banana apple|apple |
2 |1234567890       |orange|
3 |                |345   |
4 |                |XYZ   |

Formula uses A1 for original text, B1 and B2 for SUBSTITUTE words, A2 for original number string, and B4 for REPLACE text.

Result

    C                  
1 |orange banana orange | 123XYZ7890|

Cell C1 shows the combined result: all 'apple' replaced by 'orange' and characters 4-6 replaced by 'XYZ' in the number string.

Sheet Trace Quiz - 3 Questions

What does SUBSTITUTE(A1, B1, B2) do?

A Replaces 'banana' with 'apple' in A1

B Replaces first 3 characters in A1 with 'orange'

C Replaces all 'apple' with 'orange' in A1

D Deletes 'apple' from A1

Key Result

SUBSTITUTE(text, old_text, new_text) replaces all old_text with new_text; REPLACE(text, start_num, num_chars, new_text) replaces characters starting at start_num for num_chars with new_text.