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

Examples

Notes

Syntax

[] 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.