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.


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.


=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
  • text_to_search = text (or cell reference with text data) that you want to replace text within
  • search_for = case sensitive text (or cell reference with text data) that you want to search for within text_to_search
  • replace_with = text (or cell reference with text data) that will replace search_for.
  • [occurrence_number] = optional setting to replace only the occurrence number provided (e.g. if search_for appears in text_to_search twice, only the nominated occurrence will be replaced). By default the function replaces all occurrences.


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.
1Dwight L. Chang
2Dwight Fart Schrute
3Kelly and Kelly
Next Lesson
hand pointing emoji hand pointing emoji


Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

You'll get updates from me with an easy-to-find "unsubscribe" link.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

You'll get updates from me with an easy-to-find "unsubscribe" link.