Last Updated on October 31, 2023 by Jake Sheridan
In this tutorial, you will learn how to use the SUBSTITUTE function in Google Sheets.
How to Use the SUBSTITUTE Function in Google Sheets
The SUBSTITUTE function in Google Sheets allows you to replace text in a cell with a different text string. This can be incredibly helpful when you need to quickly make changes to large amounts of text data.
For example, you may need to replace a certain word or character with another. The SUBSTITUTE function also gives users the option to replace all instances of the string or just a specific occurrence.
In this guide, we will show you how to use the SUBSTITUTE function in Google Sheets and provide examples of how it can be used.
How to Replace A Character or Word in Google Sheets
Here’s how to replace a character or word in Google Sheets.
First, select the cell you want to place the SUBSTITUTE function.
In this example, we will place the output of our SUBSTITUTE function in column E. We’ve added three columns to help supply the arguments we need for the function.
Type the string “=SUBSTITUTE” to start using the SUBSTITUTE function in Google Sheets.
Next, we’ll add the following arguments: the original text, the text to search for, the text to replace the found text with, and the occurrence to replace.
The fourth argument is optional. If left blank, Google Sheets will automatically change all instances found in the original string.
Hit the Enter key to return the updated string.
In the example above, we were able to replace all instances of ‘o’ in the string ‘Hello World’ with a 0 instead.
We can also specify the occurrence to change a specific instance of a string rather than every occurrence in the target text.
This guide should be everything you need to use the SUBSTITUTE function in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.
More ways to use text in Google Sheets:
- Add Bullet Points
- Insert Text Box
- Insert Special Characters
- Insert Symbols
- Insert Page Break
- Center Text
- How To Make Text Fit In Cell
- Make Text Vertical
- Change Case
- View Page Breaks
- Line Break In Google Sheets
- How To Capitalize
- How To Wrap Text
- How To Indent
- Count Cells with Text
- Fit Text to Cell
- Checking if a Cell Contains a Substring
- TEXT Function
- Combine Text From Two Cells
- SUMIF Cells If Contains Text
- Add Text To Formula
- Indent Text
- Change Text To All Caps
- Spell Check in Google Sheets
- How to Use Spell Check
- Insert a Bullet List
- Make a Numbered List
- Insert Bullet Points
- Add a New Line In Cell
- Add Multiple Lines In One Cell
- Wrap Text In Cell
- Superscript In Google Sheets
- Make A Degree Symbol
- How To Alphabetize