How to Use the SUBSTITUTE Function in Google Sheets

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.

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 function
• SUBSTITUTE() This is the function. In order to use it, we need to write the following attributes text_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 the search_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

1. Make sure to always separate each attribute with a comma “,” for your SUBSTITUTE function to work properly.
2. 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.
3. 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

1. In the sample spreadsheet below click on the empty cell (D7) to activate it.

 

2. Input =SUBSTITUTE into the cell.

 

3. Add an open parantheses symbol “(“. Google sheets should now show a summary of the SUBSTITUTE function and the attributes you need to input next.

 

4. For this example, we will select C7 as the text_to_search. You can either click on the cell or input it manually.

 

5. Don’t forget to add a comma “,” here before inputting the next attribute.

 

6. Google Sheets should now prompt for you to input the search_for attribute. In this example, we will be inputting “7:00”.

 

7. Add another comma “,” here before entering the third and final attribute.

 

8. Finally, input “8:00” as the replace_with text.

 

9. 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”.

1. In the sample spreadsheet below click on the empty cell (D2) to activate it.

 

2. Input =SUBSTITUTE into the cell.

 

3. Add an open parantheses symbol “(“. Google sheets should now show a summary of the SUBSTITUTE function and the attributes you need to input next.

 

4. For this example, we will select C2 as the text_to_search. You can either click on the cell or input it manually.

 

5. Don’t forget to add a comma “,” here before inputting the next attribute.

 

6. Google Sheets should now prompt for you to input the search_for attribute. In this example, we will be inputting “:00”.

 

7. Add another comma “,” here before entering the third and final attribute.

 

8. Finally, input “” as the replace_with text. This will replace the search_for text with nothing, effectively deleting it.

 

9. 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”.

1. In the sample spreadsheet below click on the empty cell (D2) to activate it.

 

2. Input =SUBSTITUTE into the cell.

 

3. Add an open parenthesis symbol “(“. Google sheets should now show a summary of the SUBSTITUTE function and the attributes you need to input next.

 

4. For this example, we will select C2 as the text_to_search. You can either click on the cell or input it manually.

 

5. Don’t forget to add a comma “,” here before inputting the next attribute

 

6. Google Sheets should now prompt for you to input the search_for attribute. In this example, we will be inputting “9:00”.

 

7. Add another comma “,” here before entering the third attribute.

 

8. Input “10:00” as the replace_with text

 

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

 

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

 

11. 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. 🙂