SUBSTITUTE() Function - Change Specific Text within a Cell in Excel

Add to Favorites
Author: | Edits: don

Change or replace text in a cell with other text - you can replace a single character, numbers, letters, etc.

This allows you to do things like replace all dashes with spaces, or vice versa, replace a word or a number in a cell, and even to replace all instances or a word, number, or character in a cell or a list of cells.

To do this, we use the SUBSTITUTE() function in Excel.







=SUBSTITUTE(text, old_text, new_text, [instance_num])
Argument Description

The text or the cell containing the text that has something you want to replace.


The specific characters/text that you want to replace.


The specific characters/text that you want to use to replace the Old_text.


Optional argument. Says which instance of Old_text you want to replace. If the Old_text value appears multiple times, you can use this argument to say which one you want to replace or leave this argument empty and all occurrences of the Old_text will be replaced.

[] means the argument is optional.


Replace a Word

When replacing text, you must include quotation marks around the text within the SUBSTITUTE() function.




Replace Numbers

You do not need to put quotes around numbers when replacing them.




You can also replace a specific number inside a number.




Replace Non-Text Characters

Here, we will replace a space with a dash. Note, both characters are considered "text", which means that they must be surrounded with quotation marks in the SUBSTITUTE() function.




Replace a Specific Instance of Something

Let's replace the second "is" in the below example with nothing; effectively, we are removing the second "is" from the text in the cell.




Note that I replaced " is" instead of just "is" and this was done to avoid having an extra space leftover in the result.

This technique, removing text using the SUBSTITUTE() function, is used in much more complex and powerful formulas that you can create in Excel. As a result, always keep in mind that you can use this function to remove instances of text/characters from a cell.


The SUBSTITUTE() function is a great function in Excel to use when you need to edit the contents of a cell. This is especially helpful when you are doing something like working with a list of part numbers that follow a similar pattern but now they all need to be slightly changed.

I use the word "replace" a lot in this tutorial because that is the best word to describe what is happening; however, there is a REPLACE() function in Excel and that is not to be confused with the SUBSTITUTE() function. The REPLACE() function allows you to replace characters when you know their starting and ending position within a cell; in certain instances this is necessary, but it is rarely used compared with the SUBSTITUTE() function.

Make sure to download the attached file so you can work with these examples in Excel.

Excel Function: SUBSTITUTE()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.