Find and replace in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

Column A contains text values with some containing the word 'apple'. Column B categorizes the items.

Cell	Value
A1	apple
A2	banana
A3	apple pie
A4	grape
B1	fruit
B2	fruit
B3	dessert
B4	fruit

Formula Trace

=ARRAYFORMULA(SUBSTITUTE(A1:A4, "apple", "orange"))

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

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

Step 3: SUBSTITUTE("apple pie", "apple", "orange")

Step 4: SUBSTITUTE("grape", "apple", "orange")

Step 5: ARRAYFORMULA({"orange"; "banana"; "orange pie"; "grape"})

Cell Reference Map

    A         B
1 | apple     | fruit  |
2 | banana    | fruit  |
3 | apple pie | dessert|
4 | grape     | fruit  |

Formula references cells A1 to A4 for substitution.

The formula uses the text values in cells A1 through A4 to find and replace 'apple' with 'orange'.

Result

    A           B
1 | orange    | fruit  |
2 | banana    | fruit  |
3 | orange pie| dessert|
4 | grape     | fruit  |

The formula output replaces 'apple' with 'orange' in column A's text.

The result shows the replaced text in the same order as the original data, with 'apple' changed to 'orange'.

Sheet Trace Quiz - 3 Questions

What does the SUBSTITUTE function do in this formula?

A Replaces 'apple' with 'orange' in each cell of A1:A4

B Deletes all text in A1:A4

C Adds 'orange' after each word in A1:A4

D Counts how many times 'apple' appears

Key Result

ARRAYFORMULA combined with SUBSTITUTE applies text replacement across a range of cells.