Formulas > =SUBSTITUTE()

How To Use SUBSTITUTE() Function in Google Sheets


Replaces existing text with new text in a string.

Common questions about the SUBSTITUTE formula in Google Sheets:
What does the SUBSTITUTE formula do? replace text with other text.
How do I use the SUBSTITUTE formula in Google Sheets? write the equal sign and then SUBSTITUTE. By the time you type in sub you probably get the autofill to get it in the cell without having to type the whole thing.
Can the SUBSTITUTE formula replace multiple instances of a specific text? Yes
How can I use the SUBSTITUTE formula with case sensitivity? Might want to try REGEXREPLACE instead.
Can the SUBSTITUTE formula be used with wildcards or regular expressions? Might want to try REGEXREPLACE instead.

Appropriate use of the SUBSTITUTE formula in Google Sheets:
Replacing specific text within a cell or a range of cells.
Correcting data entry errors or inconsistencies.
Removing unwanted characters or symbols from data.
Reformatting data by substituting one text pattern with another.

Common mistyping of the SUBSTITUTE formula:
Misspelling the function name as "SUBSTITUE" or "SUBSITUTE."
Incorrectly capitalizing the function name as "substitute" or "Substitute."

Common inappropriate use of the SUBSTITUTE formula:
Using the SUBSTITUTE formula on entire columns or large ranges unnecessarily, which can slow down the spreadsheet's performance.
Applying the SUBSTITUTE formula without considering the context or purpose of the data, leading to unintended changes.
Using the SUBSTITUTE formula on numeric values, as it only works on text.

Common pitfalls when using the SUBSTITUTE formula:
Forgetting to include the cell reference or text within quotation marks.
Failing to use the correct syntax or parameters of the SUBSTITUTE formula.
Overlooking the fact that the SUBSTITUTE formula only replaces the first occurrence of the specified text by default.

Common mistakes when using the SUBSTITUTE formula:
Not anchoring the cell references correctly when using the SUBSTITUTE formula within other formulas.
Neglecting to update formulas that include the SUBSTITUTE formula when inserting or deleting rows or columns.
Using the SUBSTITUTE formula to replace text that could be better handled with other functions like REGEXREPLACE or FIND/REPLACE.

Common misconceptions about the SUBSTITUTE formula:
Believing that the SUBSTITUTE formula automatically replaces either all occurrences of the specified text, or just the first one.
Assuming that the SUBSTITUTE formula can handle complex text patterns or regular expressions, which requires additional functions or techniques.
Remember that the specific usage and best practices for the SUBSTITUTE formula may vary depending on your specific needs and the data you are working with in Google Sheets.

Google Sheet Formula Frustrations Solved
Find every formula you'll ever need in Google Sheets here at Better Sheets. Whether you're a beginner or an advanced user, I’ve got you covered with a comprehensive guide of 504 formulas.

Are you struggling to find a specific value in a column of data? Look no further than the powerful VLOOKUP formula. Or maybe you need to calculate the sum of values that meet specific criteria - try out SUMIF. And when it comes to frequency of values, COUNTIF has you covered.

Have you heard of the mysterious and powerful IF formula? It can turn your spreadsheets into gateways of productivity. And don't forget about the oft-partner ISBLANK(). 

Find step-by-step tutorials for any formula here on Better Sheets. Every formula page comes with links to written blog posts and Better Sheets tutorials featuring the exact formula.

Looking for a way to segment data based on specific criteria? The FILTER formula is perfect for you. 

If you need to replace VLOOKUP, give INDEX/MATCH a try.

At, I have everything you need to take your Google Sheets skills to the next level. 
From ARRAYFORMULA() to ZTEST(), Better Sheets has it all. 

How To Actually Use SUBSTITUTE() in Sheets

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

11Better Sheets Tutorials

Introductng a new tool from Better Sheets. A free Google Sheets™️ Add-On to parse spin syntax content.

Revolutionize Your Google Sheets writing with Spin Tax Content Spinner! Say Goodbye to Tedious Editing. Get All the Variations You Need. 

  • Spin randomly to get one variation
  • Spin text for all variations
  • Spin text for all variations including duplicates

Spintax is written with pipes separating content you want to randomly select. {Hi|Hello|Hey} would either be Hi, or Hello, or Hey.
'll show you a game-changing Google Sheet formula that will make your day, especially if you're into bulk publishing and chat GPT. With this formula, you'll be able to insert lists of keywords into prompts effortlessly. I'll guide you step-by-step on how to set it up and use it effectively. Get ready to become faster, better, and more efficient in your publishing tasks. Let's dive in!
Add the image of a youtube thumbnail with only a YouTube url into your Google Sheets. Great use case for Substitute() or Split() functions. Nice way to add a little visual flair to your sheets.
One of the most requested videos, Ever. How to put data, a number, some text, from a Google Sheets onto a website without having to embed the entire Google Sheet. This is the way!
Embed a little HTML into your site to capture Emails straight to a Google Sheet. I've written the apps script and the html and the javascript for you. As easy as copy and pasting. Use my referral link if you choose to use Carrd for this:
Create a hook writing sheet with SUBSTITUTE(). Create an engine to write tweet upon tweet upon tweet. Bundle up 100 hooks/formats and sell it. Go ahead, make sweet tweets in a sweet sheet.
Create a ChatGPT prompt inside a sheet, edit that prompt with a single input 100 times over. In this case we're creating a twitter thread prompt and replacing the topic 100 times. so you can use 1 prompt to run 100 ai calls, and then we go further

We make it better.

We add a checkbox and another prompt to take the output of the 1st prompt and run it again. And we can do that 100 times too. So each horizontal row can be it's own ChatGPT.

You can get the ai Apps Script in this video's sheet or in the sheet connected to the GPT-4 upgrade:
Walk through step-by-step going from a blank sheet to an AI powered prompt engineering cheat sheet of a spreadsheet.

Watch the Upgrade to GPT-4 video to see the differences between GPT-3 and GPT-4 Apps Script

In this sheet review I take another YouTuber's ChatGPT prompt builder through the Better Sheets Sheet Makeover process. We make it more useful and more branded. Make it really look and feel cool to use. Saves users time. And makes them want to share it with others.
Create your own AI writer with these google sheets tips and tricks and code.

Learn more about the SUBSTITUTE() formula:

Count text characters in a string

Google Sheets use Len and Substitute to count comma separated elements in a cell.

Generate a SUBSTITUTE() formula for your needs with AI

Google Sheets Formula Generator

Whatever you need to do in sheets, you can generate a formula. Use the Better Sheets Formula generator to create a formula for any need. Completely free for members.


Looking for more help inside sheets get the free Add-on: Asa. Ask Sheets Anything. Go ahead, ask it any problem you migth have. Bring your own APIKEY and generate formulas inside of Google Sheets.