SUBSTITUTE and REPLACE in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

Sample sentences in column A, replacement words in column B, and formulas in column C demonstrating SUBSTITUTE and REPLACE functions.

Cell	Value
A1	I like apples
A2	I like apples and apples
A3	I like apples
B1	oranges
B2	bananas
B3	8
C1	SUBSTITUTE(A1, "apples", B1)
C2	SUBSTITUTE(A2, "apples", B2, 2)
C3	REPLACE(A3, B3, 6, "oranges")

Formula Trace

SUBSTITUTE(A2, "apples", B2, 2)

Step 1: A2

Step 2: "apples"

Step 3: B2

Step 4: instance_number = 2

Step 5: Find 2nd occurrence of 'apples' in 'I like apples and apples'

Step 6: Replace 2nd 'apples' with 'bananas'

Cell Reference Map

     A               B          C
1 | I like apples | oranges | SUBSTITUTE(A1, "apples", B1)
2 | I like apples and apples | bananas | SUBSTITUTE(A2, "apples", B2, 2)
3 | I like apples | 8        | REPLACE(A3, B3, 6, "oranges")

Cells A1:A3 contain original text, B1:B3 contain replacement text or position, C1:C3 contain formulas using SUBSTITUTE and REPLACE referencing these cells.

Result

     A               B          C
1 | I like apples | oranges | I like oranges
2 | I like apples and apples | bananas | I like apples and bananas
3 | I like apples | 8        | I like oranges

Column C shows the results of the formulas: C1 replaces all 'apples' with 'oranges', C2 replaces only the 2nd 'apples' with 'bananas', C3 replaces 6 characters starting at position 8 with 'oranges'.

Sheet Trace Quiz - 3 Questions

What does SUBSTITUTE(A1, "apples", B1) do?

A Replaces the 2nd 'apples' in A1 with 'oranges'

B Replaces only the first 'apples' in A1 with 'oranges'

C Replaces all 'apples' in A1 with 'oranges'

D Replaces 6 characters starting at position 3 with 'oranges'

Key Result

SUBSTITUTE(text, old_text, new_text, [instance_num]) replaces occurrences of old_text; REPLACE(text, start_num, num_chars, new_text) replaces characters by position.