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.

Sections:

Syntax

Examples

Notes

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.

983d44840d6f8521cc209450388d4cba.png

Result:

9d20b3267bfae13e7da84a24f9a7c575.png

Replace Numbers

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

b6ac8ea313a2eeb7ee37dcd40d8c23fa.png

Result:

25b9ad4e6adc3262883d43c9694d1509.png

You can also replace a specific number inside a number.

2f51cc9b9bab2cd99ebb638f60c6d4a1.png

Result:

03571ffb8e7af253accb50bf2e74dbe9.png

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.

f5322a63398def3932d8329fa359117d.png

Result:

28b24ac9497dacc2f08fa55946a90f71.png

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.

e80fdd1ec45271bd89bada9eece17ce6.png

Result:

ec85862b8992e5dc7130b67a440eee56.png

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.


Excel Function: SUBSTITUTE()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Excel Function: SUBSTITUTE()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course