How to use SUBSTITUTE function in Excel?

The SUBSTITUTE function in Excel is used to replace occurrences of a specific text string within another text string. It’s especially useful for modifying text data in cells. The basic syntax of the SUBSTITUTE function is:

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

Here’s a breakdown of the parameters:

text: The original text or cell reference containing the text you want to modify.
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. If omitted, all occurrences of `old_text` in `text` will be replaced with `new_text`.

Example Usage

Basic Replacement:

Suppose cell A1 contains the text “apple banana apple”. If you want to replace “apple” with “orange”, you would use:

   =SUBSTITUTE(A1, "apple", "orange")

Result: “orange banana orange”

Replacing a Specific Instance:

If you only want to replace the second occurrence of “apple”, use the `instance_num` parameter:

   =SUBSTITUTE(A1, "apple", "orange", 2)

Result: “apple banana orange”

Case Sensitivity:

The SUBSTITUTE function is case-sensitive. If you have “Apple” in a string and you specify “apple” in `old_text`, it will not be replaced. Ensure the cases match or use other functions like `LOWER` or `UPPER` to manipulate cases if necessary.

Practical Tips

Use SUBSTITUTE to clean up data, such as removing unwanted characters or correcting common typos.
Embed it within other functions for more complex manipulations, like combining with `TRIM`, `LEN`, or `MID` for more sophisticated text editing.
When working with numbers stored as text, SUBSTITUTE can help remove or replace characters like dashes or spaces before converting them to numeric values with functions like `VALUE`.

By understanding and applying the SUBSTITUTE function correctly, you can significantly enhance your text data manipulation capabilities in Excel.

facebooklinkedin