Google Sheets 101: Replacing Text

LESSONS

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.

=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.

FREE RESOURCE

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

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