SUBSTITUTE and REPLACE in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUBSTITUTE function do in Excel?

SUBSTITUTE replaces specific text in a string with new text. It changes all or selected occurrences of the old text without changing the rest of the string.

beginner

How does the REPLACE function differ from SUBSTITUTE?

REPLACE changes part of a text string based on position and length, not by matching text. It replaces characters starting at a specific position with new text.

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: text to insert

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)<br>This changes only the 2nd "apple" to "orange" resulting in "apple, orange, apple".

Which function replaces text based on position and length?

A SUBSTITUTE

B REPLACE

C CONCATENATE

D LEFT

REPLACE changes text starting at a specific position for a given length.

What does the optional 4th argument in SUBSTITUTE do?

A Specifies the new text

B Specifies the number of characters to replace

C Specifies which occurrence of old text to replace

D Specifies the start position

The 4th argument tells SUBSTITUTE which occurrence of the old text to replace.

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

A REPLACE(text, 1, 3, new_text)

B SUBSTITUTE(text, old_text, new_text, 1)

C LEFT(text, 3)

D RIGHT(text, 3)

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 banano

D bonana

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

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

A REPLACE

B LEFT

C RIGHT

D SUBSTITUTE with instance_num=3

SUBSTITUTE lets you specify which occurrence to replace using the 4th argument.

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

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

Describe the syntax and purpose of the optional argument in SUBSTITUTE.

This argument controls how many replacements happen.