SUBSTITUTE and REPLACE in Google Sheets - Step-by-Step Guide

Introduction

These two formulas help you change text inside a cell. SUBSTITUTE replaces specific words or letters you choose. REPLACE changes part of the text based on position. They solve the problem of fixing or updating text without typing it all again.

When you want to change all occurrences of a word in a sentence, like fixing a repeated typo.

When you need to replace only the second or third occurrence of a word in a text.

When you want to change a part of a phone number or code by position, like changing area code digits.

When you want to update a product code by replacing certain characters at fixed positions.

When you want to remove or replace a specific word in a sentence without changing the rest.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- the formula bar

Formula bar shows your typed formula

💡 Start with =SUBSTITUTE(text, old_text, new_text, [instance_num]) or =REPLACE(text, start_num, num_chars, new_text)

Step 3: For SUBSTITUTE, enter the text or cell reference as the first argument

- inside the parentheses

Formula knows which text to change

Step 4: Type the word or letter you want to replace as the second argument

- inside the parentheses after the first argument

Formula knows what to find in the text

Step 5: Type the new word or letter as the third argument

- inside the parentheses after the second argument

Formula knows what to replace the old text with

Step 6: Optionally, for SUBSTITUTE, type the instance number to replace only that occurrence

- inside the parentheses as the fourth argument

Only that specific occurrence is replaced

Step 7: For REPLACE, enter the text or cell reference as the first argument

- inside the parentheses

Formula knows which text to change

Step 8: Type the starting position number of the text to replace as the second argument

- inside the parentheses after the first argument

Formula knows where to start replacing

Step 9: Type how many characters to replace as the third argument

- inside the parentheses after the second argument

Formula knows how many characters to remove

Step 10: Type the new text to insert as the fourth argument

- inside the parentheses after the third argument

Formula replaces the specified characters with this new text

Step 11: Press Enter

- formula bar

Cell shows the updated text with replacements

Before vs After

Before

Cell A1 contains 'I love apples and apples are sweet'

After

Using =SUBSTITUTE(A1, "apples", "oranges") results in 'I love oranges and oranges are sweet'

Settings Reference

text

📍 first argument in SUBSTITUTE or REPLACE formula

The original text where replacements happen

Default: none

old_text

📍 second argument in SUBSTITUTE formula

The text you want to replace

Default: none

new_text

📍 third argument in SUBSTITUTE or fourth in REPLACE formula

The text to replace with

Default: none

instance_num

📍 optional fourth argument in SUBSTITUTE formula

Which occurrence of old_text to replace

Default: all occurrences replaced

start_num

📍 second argument in REPLACE formula

Position to start replacing characters

Default: none

num_chars

📍 third argument in REPLACE formula

Number of characters to replace starting at start_num

Default: none

Common Mistakes

Not using quotes around text strings in formulas

Google Sheets treats unquoted text as cell references, causing errors

Always put text strings inside double quotes, like "apples"

Using SUBSTITUTE without specifying instance_num when you want to replace only one occurrence

All occurrences get replaced, not just one

Add the fourth argument with the occurrence number, e.g., =SUBSTITUTE(A1, "apples", "oranges", 2)

Using REPLACE with wrong start_num or num_chars causing unexpected text changes

Formula replaces wrong part of text or deletes too much

Count characters carefully and test with small examples before applying

Summary

SUBSTITUTE replaces specific text by matching words or letters you choose.

REPLACE changes text based on position and length inside the string.

Remember to use quotes for text and count positions carefully for REPLACE.

Practice

1. What does the SUBSTITUTE function do in Google Sheets?

easy

A. It formats cells with colors.

B. It changes text based on position and length.

C. It adds numbers together.

D. It replaces specific text by matching exact words or letters.

Solution

1. Step 1: Understand SUBSTITUTE purpose

   The SUBSTITUTE function looks for specific text and replaces it exactly where it matches.

2. Step 2: Compare with REPLACE

   Unlike REPLACE, SUBSTITUTE does not use position but matches text content.

3. Final Answer:

   It replaces specific text by matching exact words or letters. -> Option D

4. Quick Check:

   SUBSTITUTE = Replace by matching text [OK]

Hint: SUBSTITUTE changes text by matching words, not positions [OK]

Common Mistakes:

• Confusing SUBSTITUTE with REPLACE
• Thinking SUBSTITUTE works by position
• Assuming SUBSTITUTE changes numbers

2. Which of these is the correct syntax for the REPLACE function in Google Sheets?

easy

A. REPLACE(text, start_num, num_chars, new_text)

B. REPLACE(text, old_text, new_text)

C. REPLACE(old_text, new_text, instance_num)

D. REPLACE(text, new_text, start_num)

Solution

1. Step 1: Recall REPLACE syntax

   REPLACE requires the original text, the start position, number of characters to replace, and the new text.

2. Step 2: Check options

   Only REPLACE(text, start_num, num_chars, new_text) matches this order and parameters correctly.

3. Final Answer:

   REPLACE(text, start_num, num_chars, new_text) -> Option A

4. Quick Check:

   REPLACE syntax = text, start, length, new text [OK]

Hint: REPLACE needs position and length, SUBSTITUTE needs old text [OK]

Common Mistakes:

• Mixing parameter order
• Using old_text instead of start_num
• Omitting number of characters to replace

3. Given cell A1 contains the text "apple banana apple", what is the result of =SUBSTITUTE(A1, "apple", "orange", 2)?

medium

A. "orange banana orange"

B. "orange banana apple"

C. "apple banana orange"

D. "apple banana apple"

Solution

1. Step 1: Understand SUBSTITUTE with instance number

   The formula replaces only the 2nd occurrence of "apple" with "orange".

2. Step 2: Apply replacement

   First "apple" stays, second "apple" changes to "orange" resulting in "apple banana orange".

3. Final Answer:

   "apple banana orange" -> Option C

4. Quick Check:

   SUBSTITUTE replaces 2nd 'apple' only [OK]

Hint: Use 4th SUBSTITUTE argument to replace specific occurrence [OK]

Common Mistakes:

• Replacing all occurrences instead of one
• Replacing first occurrence by mistake
• Confusing SUBSTITUTE with REPLACE

4. You want to replace the first 3 letters of the text in cell B2 with "XYZ". Which formula fixes the error in this incorrect formula: =SUBSTITUTE(B2, 1, 3, "XYZ")?

medium

A. =REPLACE(B2, "1", "3", "XYZ")

B. =REPLACE(B2, 1, 3, "XYZ")

C. =SUBSTITUTE(B2, "1", "XYZ", 3)

D. =SUBSTITUTE(B2, 3, 1, "XYZ")

Solution

1. Step 1: Identify misuse of SUBSTITUTE

   SUBSTITUTE does not use position or length, so it cannot replace by position.

2. Step 2: Use REPLACE for position-based replacement

   REPLACE takes start position, length, and new text, so =REPLACE(B2, 1, 3, "XYZ") is correct.

3. Final Answer:

   =REPLACE(B2, 1, 3, "XYZ") -> Option B

4. Quick Check:

   Use REPLACE for position-based text changes [OK]

Hint: Use REPLACE to change text by position, not SUBSTITUTE [OK]

Common Mistakes:

• Using SUBSTITUTE with numbers as positions
• Swapping parameters order
• Using quotes incorrectly around numbers

5. You have a list of product codes in column A like "AB12345". You want to replace the first two letters with "X5" and also replace all occurrences of "5" with "9" in the same formula. Which formula achieves this?

hard

A. =SUBSTITUTE(REPLACE(A1, 1, 2, "X5"), "5", "9")

B. =REPLACE(SUBSTITUTE(A1, "5", "9"), 1, 2, "X5")

C. =SUBSTITUTE(A1, LEFT(A1, 2), "X5")

D. =REPLACE(A1, 1, 2, "X5") & SUBSTITUTE(A1, "5", "9")

Solution

1. Step 1: Replace first two letters with REPLACE

   REPLACE(A1, 1, 2, "X5") changes first two letters to "X5".

2. Step 2: Replace all "5" with "9" using SUBSTITUTE

   SUBSTITUTE(..., "5", "9") changes all 5's to 9's in the updated text.

3. Step 3: Combine functions correctly

   Applying REPLACE first, then SUBSTITUTE on result is correct, matching =SUBSTITUTE(REPLACE(A1, 1, 2, "X5"), "5", "9").

4. Final Answer:

   =SUBSTITUTE(REPLACE(A1, 1, 2, "X5"), "5", "9") -> Option A

5. Quick Check:

   Chain REPLACE then SUBSTITUTE for combined changes [OK]

Hint: Nest REPLACE inside SUBSTITUTE to combine position and text changes [OK]

Common Mistakes:

• Applying SUBSTITUTE before REPLACE causing wrong results
• Concatenating results instead of nesting
• Using SUBSTITUTE to replace by position