If you have a lot of similar text entries in a spreadsheet, you might want to replace a small part of each entry.

Doing this manually would be a nightmare so spreadsheets have functions to help.

One such function is the SUBSTITUTE function.

SUBSTITUTE

Within text data, SUBSTITUTE replaces a piece of text data with a different piece of text.

It can do this once at a nominated occurrence or for every instance in the data.

Syntax

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

Example

You want to replace the word "fun" with "awesome" in the text:

This function is fun!

=SUBSTITUTE("This function is fun!", "fun", "awesome")

Output: This awesomection is awesome!

That doesn’t look right.

You have to be careful when using SUBSTITUTE as it’s indiscriminate.

It will replace search_for even when it is part of another word.

When search_for is "fun", it’s found twice:

This function is fun!

This is where the optional [occurrence_number] argument could be used to specify that the function should only change the second "fun".

=SUBSTITUTE("This function is fun!", "fun", "awesome",2)

Alternatively, you could make your formula more specific by including the exclamation point:

=SUBSTITUTE("This function is fun!", "fun!", "awesome!")

Both of these options output:

This function is awesome!

Replacing Text Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. One Word
In cell B1, enter a formula that replaces Dwight with Samuel from the text in A1.
2. Two Letters
In cell B2, enter a formula that replaces Fart with Kurt from the text in A2, subsituting as few letters as possible.
3. Second Occurrence
In cell B3, enter a formula that replaces the second Kelly with Erin from the text in A3.
ABC
1Dwight L. Chang
2Dwight Fart Schrute
3Kelly and Kelly
4
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.