How to Use the SUBSTITUTE Function in Google Sheets

In this tutorial, I will show you how to use the SUBSTITUTE Function in Google Sheets.

Having a huge record means that whenever you commit a mistake in terms of format, spelling, or word choice, it’ll be difficult to fix your error. That’s why the SUBSTITUTE Function in Google Sheets is so important.

Being a function that can easily replace a part within a larger piece of a string, SUBSTITUTE lets you update information, that will otherwise take a big amount of time and energy, effortlessly. That is if you understand how it works and how to use it best.

How to Use the SUBSTITUTE Function in Google Sheets

1. Identify the text_to_search parameter or the string that will be searched
2. Define the search_for parameter or the text that will be searched for
3. Declare the replace_with parameter or the replacement text
4. Select a cell where the substituted string will be put into
5. Type in the formula: “=SUBSTITUTE(text_to_search, search_for, replace_with)”
6. Hit ENTER or select another cell to finish

Using the SUBSTITUTE Function in Google Sheets Video

Defining the SUBSTITUTE Function in Google Sheets

Basically, what the SUBSTITUTE Function in Google Sheets does is that it “substitutes”, replaces, or changes an existing text or a part of it within a whole string with an entirely different set of characters or a whole new word.

The syntax of SUBSTITUTE is

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

SUBSTITUTE Function Elements Explained

• text_to_search – is the whole string to be searched
• search_for – is the set of characters within text_to_search that will be replaced 
• replace_with – is the new text that will be used to replace search_for
• [occurrence_number] – is an optional parameter that is used to define the order of the instance of search_for within text_to_search that will be replaced by replace_with if there are multiple instances of search_for

I’ve listed some examples below on how to best use the SUBSTITUTE Function in Google Sheets.

5 Examples of the SUBSTITUTE Function in Google Sheets

SUBSTITUTE is best used either to replace or remove a set of characters from a larger piece of string. It is best used for large datasets where a change in format, spelling, format, etc. needs to be applied.

Below are some common examples of using the SUBSTITUTE Function in Google Sheets.

1. Replacing Abbreviations and Symbols with their Full Term

The first example here shows the basic way to use the SUBSTITUTE Function in Google Sheets.

You take a string, conduct a search on it with SUBSTITUTE, and replace it with something else.

For the example below, I have personnel data where Gender was listed as either “M” for “Male” and “F” for “Female. That said, a program was created that only recognizes the words “Male” and “Female” and I have to update my records as such.

I was able to accomplish this with SUBSTITUTE.

As for the next example, a marketing platform restricts email addresses from being posted on free accounts and you have to pay for the privilege of entering them.

A common workaround in these online marketplaces is to mask email address identifiers such as the “@” symbol.

In using the SUBSTITUTE Function in Google Sheets I was able to mask the “@” symbol and replace it with its full term enclosed in square brackets.

You may sometimes see this in popular social media sites done by small-time marketers and online sellers.

2. Removing a Text From a String

In the example below, I got a dataset from a survey where the “First Name” and “Last Name” values were appended with labels “FN:” and “LN:” respectively. This is to distinguish the first name from the last but that affects how my system handles the names.

To resolve this I used the SUBSTITUTE Function in Google Sheets to replace “FN:” and “LN:” with “” or the symbol for a blank effectively deleting the labels.

This method of using SUBSTITUTE is really useful when you want to clean up your data of unnecessary characters or texts.

3. Replacing Multiple Pieces of Text from a Single String

You won’t always get an ideal text to work with using the SUBSTITUTE Function in Google Sheets and sometimes you’ll need to work on multiple pieces of text from a single string.

In the example below, this personnel record contains the name of people with their associate phone numbers. That said the company wishes to use a platform where the numbers 1, 2, and 3 are not accepted.

To circumvent this, we need to replace 1s, 2s, and 3s in all the phone numbers with identifiers that will still give a reader the same values. And to resolve this, I assigned each number’s word equivalent as [one], [two], and [three] respectively.

The issue this time is how will you apply all three SUBSTITUTE requirements at the same time and the answer to that is through NESTED SUBSTITUTION.

All we basically need to do is to make a SUBSTITUTION as the text_to_search parameter of the next SUBSTITUTION Function depending on how many substitutions you would need.

For the example below, I used three SUBSTITUTE Functions for 1, 2, and 3.

Changing each phone number is not only tiring but can be extremely confusing. It’s a great thing that I was able to take advantage of the SUBSTITUTE Function like this.

4. Replacing Special Characters in a String

This example is similar to a previous one but in this case, we’re dealing with special characters.

So other than having the option to enclose special characters in double quotes, we can also use the CHAR Function. To know which number to use, you can refer to the Unicode Website or you may also enter this formula on cell A1 on a new sheet:

=ARRAYFORMULA(CHAR(ROW(A1:A)))

This will give you all the special characters in column A. To find a specific symbol, use CTRL + F (⌘ + f on a Mac) and use its row number as the value for the CHAR Function.

In the next example, I replaced all forward slashes (/) with a dash (-).

This is convenient, especially for special characters that you can’t normally find on a standard keyboard such as the degree symbol and the euro sign.

5. Using SUBSTITUTE with ARRAYFORMULA

Lastly, similar to a lot of others, we can also use ARRAYFORMULA with the SUBSTITUTE Function in Google Sheets.

Below is an example that I created to show the combination of using an ARRAYFORMULA, nested SUBSTITUTION Functions, special characters, and replacement methods.

Using the SUBSTITUTE Function in Google Sheets is not as complicated compared to how useful it is. Mastering its usage can help you with data manipulation and cleanup, especially for the largest datasets.

Frequently Asked Questions about How to Use the SUBSTITUTE Function in Google Sheets

Conclusion on How to Use the SUBSTITUTE Function in Google Sheets

You can use the SUBSTITUTE Function in Google Sheets by first identifying the text_to_search parameter or the string that will be searched and then defining the search_for parameter or the text that will be searched for. Declare the replace_with parameter or the replacement text and then select a cell where the substituted string will be put into. Lastly, type in the formula: “=SUBSTITUTE(text_to_search, search_for, replace_with)” and hit ENTER or select another cell to finish.