Substituting Text Strings in Excel Formula

Text strings are sequences of characters that can represent words, sentences, numbers, or symbols. In Excel, you may often need to manipulate text strings for various purposes, such as changing the case, formatting, or replacing some parts of the text with other text.

In this article, we will focus on how to use the SUBSTITUTE function in Excel to replace specific text in a text string with another text. We will also compare it with the REPLACE function, which can replace any text that occurs in a specific location in a text string.

The SUBSTITUTE function has the following syntax:

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

The arguments are:

• text – the original text or a reference to a cell containing text for which you want to substitute characters.
• old_text – the text you want to replace.
• new_text – the text you want to replace old_text with.
• instance_num – optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

The SUBSTITUTE function returns a new text string with the specified substitution.

The REPLACE function has the following syntax:

=REPLACE(old_text, start_num, num_chars, new_text)

The arguments are:

• old_text – the original text or a reference to a cell containing text in which you want to replace some characters.
• start_num – the position of the first character within old_text that you want to replace.
• num_chars – the number of characters you want to replace.
• new_text – the replacement text.

The REPLACE function returns a new text string with the specified replacement.

Procedures

To use the SUBSTITUTE function in Excel, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =SUBSTITUTE( and then select or type the text argument.
3. Type a comma (,) and then select or type the old_text argument.
4. Type another comma and then select or type the new_text argument.
5. Optionally, type another comma and then select or type the instance_num argument.
6. Type a closing parenthesis ()) and press Enter.

To use the REPLACE function in Excel, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =REPLACE( and then select or type the old_text argument.
3. Type a comma and then select or type the start_num argument.
4. Type another comma and then select or type the num_chars argument.
5. Type another comma and then select or type the new_text argument.
6. Type a closing parenthesis and press Enter.

Example

Suppose you have a list of product names in column A, and you want to replace the word “Pro” with “Plus” in the names. You can use the SUBSTITUTE function to do this, as shown in the following table:

Alternatively, you can use the REPLACE function to do the same thing, but you need to specify the position and the number of characters to replace, as shown in the following table:

Note that the REPLACE function requires you to adjust the start_num argument depending on the length of the product name, while the SUBSTITUTE function does not.