Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
SUBSTITUTE() Function - Change Specific Text within a Cell in Excel
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.
Sections:
Syntax
SUBSTITUTE() Function
=SUBSTITUTE(text, old_text, new_text, [instance_num])
| Argument | Description |
|---|---|
| Text |
The text or the cell containing the text that has something you want to replace. |
|
Old_text |
The specific characters/text that you want to replace. |
|
New_text |
The specific characters/text that you want to use to replace the Old_text. |
|
[Instance_num] |
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.
Examples
Replace a Word
When replacing text, you must include quotation marks around the text within the SUBSTITUTE() function.
Result:
Replace Numbers
You do not need to put quotes around numbers when replacing them.
Result:
You can also replace a specific number inside a number.
Result:
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.
Result:
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.
Result:
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.
Notes
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.
