Change Specific Text within a Cell in Excel

Add to Favorites

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])

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


Excel Function: SUBSTITUTE()
Downloadable Files: Excel File

Similar Content on TeachExcel
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Generate Random Numbers within a Range in Excel
Tutorial: How to generate random whole numbers (integers) that are between two numbers.  This allows...
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a...
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This free Excel UDF (user defined function) returns the first word from a cell in Exce...
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...