SUBSTITUTE and REPLACE in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUBSTITUTE function do in Google Sheets?

SUBSTITUTE replaces all or specific occurrences of a text string within another text string with a new text string.

beginner

How does the REPLACE function differ from SUBSTITUTE?

REPLACE changes part of a text string based on position and length, while SUBSTITUTE replaces text based on matching content.

beginner

Syntax of SUBSTITUTE function?

SUBSTITUTE(text, old_text, new_text, [instance_num])<br> - text: original text<br> - old_text: text to replace<br> - new_text: replacement text<br> - instance_num (optional): which occurrence to replace

beginner

Syntax of REPLACE function?

REPLACE(old_text, start_num, num_chars, new_text)<br> - old_text: original text<br> - start_num: position to start replacing<br> - num_chars: number of characters to replace<br> - new_text: replacement text

intermediate

Example: How to replace the 2nd occurrence of "apple" with "orange" in "apple, apple, apple" using SUBSTITUTE?

Use =SUBSTITUTE("apple, apple, apple", "apple", "orange", 2) which results in "apple, orange, apple".

Which function replaces text based on position and length?

A SUBSTITUTE

B REPLACE

C CONCATENATE

D LEFT

REPLACE changes text by position and length, SUBSTITUTE replaces text by matching content.

What does the optional 4th argument in SUBSTITUTE do?

A Specifies which occurrence of old_text to replace

B Specifies the number of characters to replace

C Specifies the starting position to replace

D Specifies the new text to add

The 4th argument tells SUBSTITUTE which occurrence of old_text to replace.

If you want to replace the first 3 characters in a text, which function and arguments would you use?

A SUBSTITUTE(text, old_text, new_text, 3)

B SUBSTITUTE(text, 1, 3, new_text)

C REPLACE(text, 3, 1, new_text)

D REPLACE(text, 1, 3, new_text)

REPLACE with start_num=1 and num_chars=3 replaces the first 3 characters.

What will =SUBSTITUTE("banana", "a", "o") return?

A bonono

B banana

C bonana

D banano

SUBSTITUTE replaces all occurrences of "a" with "o", so "banana" becomes "bonono".

Which function can replace only the 3rd occurrence of a word?

A REPLACE

B RIGHT

C SUBSTITUTE with instance_num=3

D LEFT

SUBSTITUTE with the 4th argument set to 3 replaces only the 3rd occurrence.

Explain how SUBSTITUTE and REPLACE functions differ and give an example of when to use each.

Think about whether you want to replace by matching text or by position.

Describe the arguments needed for SUBSTITUTE and how to replace only the second occurrence of a word.

Remember the optional 4th argument controls which occurrence to replace.