If you’re using Excel regularly, you might find that the SUBSTITUTE function is an essential part of your spreadsheet toolbox. This function helps you replace specific text within cells with other text or remove it entirely. Here’s how to use the SUBSTITUTE function in Excel.

1. Syntax of the Substitute Function

The syntax of the SUBSTITUTE function is as follows:

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

“Text” refers to the cell containing the text you want to manipulate. “Old_text” is the segment of text you want to replace, and “new_text” is the text that will replace it. Finally, “instance_num” is an optional argument that specifies which occurrence of old_text you want to replace (the first, second, etc.).

2. Replace text within a single cell

Let’s assume you have a list of names in column A of your spreadsheet. You want to replace any instances of “Smith” with “Jones.” To do so, you’d use the following formula in cell B1:

=SUBSTITUTE(A1,”Smith”,”Jones”)

This will replace any instance of “Smith” in cell A1 with “Jones.” Copy the formula down to the rest of the cells in column B to complete the process.

3. Replace text across multiple cells

You can also use the SUBSTITUTE function to replace text across multiple cells. Let’s say you have a list of addresses in cells A1:A4, and you want to remove the street name from each address. You’d use the following formula:

=SUBSTITUTE(A1,”Road”,””)

This formula will remove any occurrence of “Road” from cell A1. Now you can copy the formula down to the cells below to apply it to all of the addresses in the list.

4. Remove characters from text strings

Sometimes you may need to remove specific characters from a text string, such as replacing whitespace or dashes with nothing. You’d use the following formula in such cases:

=SUBSTITUTE(text,” “,””)

This formula will remove any whitespace in the text string, including spaces, tabs, and line breaks.

5. Replacing multiple instances of text

If you want to replace all instances of old_text, you’d use the final parameter, “instance_num”. For example, if you have a sentence with two occurrences of “and,” you can replace both with a new text string. You’d use the formula:

=SUBSTITUTE(text,”and”,”or”,2)

This replaces the second instance of “and” with “or” in the selected text.