The SUBSTITUTE Function in Google Sheets is useful for situations where you need to replace existing text with new text.
In this guide, you will learn how the SUBSTITUTE
function can be applied to a variety of scenarios. Step-by-step instructions and visual aids will be provided to help you better understand how to apply the SUBSTITUTE
function in your day-to-day work.
Table of Contents
As stated at the beginning of this article, the SUBSTITUTE
function is used mainly for replacing existing texts in your Google Sheets with other text.
One common scenario where the SUBSTITUTE
function would be used is when you need to standardize the data in your database. For example, let’s say you are managing a spreadsheet containing the profiles of your company’s employees. However, as the data was input by multiple people, under the Gender column, some entries use correctly, “Male” or “Female” and others use “Man” or “Woman”. In this scenario, you can quickly use the SUBSTITUTE
function to change the entries instead of having to manually change every single entry.
In another example, your company could be changing the start of the night shift hours from “7:00pm” to “8:00pm” and you have to update the time accordingly in the spreadsheet tracking employee work schedules. We’ll be using this example to demonstrate how the SUBSTITUTE
function is applied in Google Sheets.
Before that, let’s move on to the next section to break down and show you how to write the SUBSTITUTE
function and its attributes.
The Anatomy of the SUBSTITUTE Function
Below is the basic syntax of the SUBSTITUTE function used in Google Sheets.
=SUBSTITUTE(“cell to search”, “search for”, “replace with”)
Let’s breakdown the syntax to properly understand how to use the function
=
The equal sign is how we tell Google Sheets that we’re writing a functionSUBSTITUTE()
This is the function. In order to use it, we need to write the following attributestext_to_search
,search_for
inside the parentheses ‘()’.text_to_search
This is the cell you want to make the changes. This tells the function which cell you want it to search for to make the needed changes.search_for
This is the text you want to replace. This tells the function which text in the cell you want to change.replace_with
This is the text you want to replace thesearch_for
text with.- occurrence_number By default, the function replaces all instances of search_for text in the text_to_search. However, there is an OPTIONAL attribute that lets you choose which instance of search_for that occurs in the text_to_search you would like to replace.
⚠ Now, a few notes to make sure your SUBSTITUTE Function works properly
- Make sure to always separate each attribute with a comma “,” for your SUBSTITUTE function to work properly.
- Keep in mind that the search_for text IS case-sensitive. If you look try to replace “Admin” but put “admin”, the function will not recognize that as an instance.
- If you do not use the occurrence number attribute, just close the parentheses after the replace_with text.
A Real Example of Using the SUBSTITUTE Function
Below is the sample database we will be using in this example to see how the SUBSTITUTE Function is used in Google Sheets.
As I mentioned in the introduction, we will be using the example of changing the “Start Time” values in a spreadsheet for tracking employee work schedules. In this scenario, we want to change the “Start Time” of the Night Shift employees from 7:00PM to 8:00PM.
Here’s a step-by-step explanation on how the SUBSTITUTE Function changes the existing “Start Time” values
- First, we select the cell of an employee in the “Start Time” column. In this case, we selected Lee’s in C7.
- In the same row, we input =SUBSTITUTE under the “New Start Time” column. This tells Google Sheets that we are utilizing the SUBSTITUTE Function.
- For the first attribute, we enter C7 as the text_to_search the function should refer to.
- Next, we add the search_for the function should look for inside C7. In this case, we will be searching for “7:00”. This tells the function which part of the text in C7 needs to be updated. Seeing that the new “Start Time” will be “8:00 PM”, we do not need to replace the “PM” part of the text.
- Finally, we input the replace_with text, “8:00PM”. This tells the function what we are going to replace “7:00” with.
- As a result, the function determines the “New Start Time” value to be “8:00PM”.
Quite straightforward right?
You can make a copy of the sample spreadsheet using the link attached below and give it a try:
How to Use the SUBSTITUTE Function in Google Sheets
- In the sample spreadsheet below click on the empty cell (D7) to activate it.
- Input =SUBSTITUTE into the cell.
- Add an open parantheses symbol “(“. Google sheets should now show a summary of the SUBSTITUTE function and the attributes you need to input next.
- For this example, we will select C7 as the text_to_search. You can either click on the cell or input it manually.
- Don’t forget to add a comma “,” here before inputting the next attribute.
- Google Sheets should now prompt for you to input the search_for attribute. In this example, we will be inputting “7:00”.
- Add another comma “,” here before entering the third and final attribute.
- Finally, input “8:00” as the replace_with text.
- Hit the “Enter” key and it will now display “8:00 PM”.
How to Use the SUBSTITUTE Function in Google Sheets to Remove Text
In this example, you need to remove the minutes from the “Start Time” values so that it reads “9 AM” instead of “9:00 AM”.
- In the sample spreadsheet below click on the empty cell (D2) to activate it.
- Input =SUBSTITUTE into the cell.
- Add an open parantheses symbol “(“. Google sheets should now show a summary of the SUBSTITUTE function and the attributes you need to input next.
- For this example, we will select C2 as the text_to_search. You can either click on the cell or input it manually.
- Don’t forget to add a comma “,” here before inputting the next attribute.
- Google Sheets should now prompt for you to input the search_for attribute. In this example, we will be inputting “:00”.
- Add another comma “,” here before entering the third and final attribute.
- Finally, input “” as the replace_with text. This will replace the search_for text with nothing, effectively deleting it.
- Hit the “Enter” key and it will now display “9 AM”
How to Use the SUBSTITUTE Function in Google Sheets With the Occurrence Number Attribute
In this example, we will be changing the schedules of employee John from “9:00 AM to 9:00 PM” to “9:00 AM to 10:00 PM”.
- In the sample spreadsheet below click on the empty cell (D2) to activate it.
- Input =SUBSTITUTE into the cell.
- Add an open parenthesis symbol “(“. Google sheets should now show a summary of the SUBSTITUTE function and the attributes you need to input next.
- For this example, we will select C2 as the text_to_search. You can either click on the cell or input it manually.
- Don’t forget to add a comma “,” here before inputting the next attribute
- Google Sheets should now prompt for you to input the search_for attribute. In this example, we will be inputting “9:00”.
- Add another comma “,” here before entering the third attribute.
- Input “10:00” as the replace_with text
- Now, if you end the function here and hit enter, you will get “10:00 AM to 10:00 PM” instead of “9:00 AM to 10:00 PM”. This is because the SUBSTITUTE Function will recognize the “9:00” from “9:00AM” as an instance as well.
- Therefore, we need to add the fourth attribute, occurrence_number. In this example, we want to replace only the second instance of “9:00” so please input 2.
- Hit the “Enter” key and it will now display “9:00 AM to 10:00 PM”
That’s it, well done for completing this tutorial. You can use the SUBSTITUTE function together with other numerous Google Sheets formulas to create even more useful formulas. 🙂

1 comment
Hey, can i use several “replacements”?
Example: In B10, i want to replace D11 with E11, D12 with E12, D13 with E13,… so i use the function “=SUBSTITUTE(B10, D11:D19, E11:E19)”. This, sadly, doesn’t work (i get the Error “An array value could not be found.”). Is there another way i can use the substitute functions once to replace several different things?