LESSONS 1. Intro 2. Cell Referencing 3. Data Types 4. Operators 5. Functions 6. Intro To Logic 7. Simple Math 8. Math & Logic 9. Changing Text 10. Replacing Text 11. Splitting Text 12. Joining Text 13. Sorting Data 14. Rearranging Data 15. Looking Up Data

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])
• 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.

### 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.
 A B C 1 Dwight L. Chang 2 Dwight Fart Schrute 3 Kelly and Kelly 4
Next Lesson

FREE RESOURCE

### Google Sheets Cheat Sheet

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

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

### Want Better-Looking Google Sheets?

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.

🗙