How to use Excel SUBSTITUTE Function

Excel SUBSTITUTE Function

When to Use the Excel SUBSTITUTE Function in Excel

The SUBSTITUTE function in Excel is your go-to tool when you need to replace specific instances of text within a larger text string. This function proves invaluable in scenarios where you want to make selective changes, remove unwanted characters, or update outdated information.

What Excel SUBSTITUTE Function Returns

The Excel SUBSTITUTE function replaces occurrences of a specified old text with new text in a given text string. You have the freedom to decide how many instances you want to replace. This makes it perfect for tasks such as standardizing data or correcting consistent typos.

Syntax of Excel SUBSTITUTE Function

The syntax of the Excel SUBSTITUTE function is as follows:

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

Input Arguments

Here are the input arguments for the Excel SUBSTITUTE function:

👉 text: The text string that contains the instances you want to replace. This can be a direct text entry, a cell reference containing text, or a formula resulting in text.

👉 old_text: The specific text you want to replace within the text argument.

👉 new_text: The text you want to substitute in place of old_text.

👉 instance_num (optional): This parameter allows you to specify which occurrence of old_text you want to replace. If omitted, all instances of old_text will be replaced.

Extra Notes

👉 If old_text is not found within the text string, no changes will occur.

👉 If you provide an instance_num that is greater than the number of occurrences of old_text, no replacement will occur.

👉 The SUBSTITUTE function is case-sensitive. “Apple” and “apple” are treated as distinct text.

Common Mistakes to Avoid

👉 Misplacing quotation marks: Ensure that you properly enclose your text entries, old and new, in double quotation marks.

👉 Omitting the new_text argument: Every instance of old_text needs a corresponding replacement defined in new_text.

👉 Incorrect instance_num: If you want to replace a specific occurrence, make sure to count correctly and provide the appropriate instance number.

Tips For Optimal Usage

To make the most of the SUBSTITUTE function, consider these tips:

👉 Combine SUBSTITUTE with other functions like FIND or LEN for more intricate text manipulations.

👉 Experiment with relative and absolute cell references to automate replacements across multiple cells.

👉 Always double-check your new_text to avoid inadvertently changing data.

Examples of the Excel SUBSTITUTE Function

Let’s walk through a few examples to demonstrate the prowess of the Excel SUBSTITUTE function.

Example 1: Basic Usage

Suppose you have a list of product names containing “pro” that you want to change to “professional”. In cell B1, input:

Remember, The “Find and Replace” option is great for quick and simple text replacements, if you need to perform more intricate replacements or apply conditional transformations, using functions like SUBSTITUTE allows you to craft more dynamic and versatile solutions.

Example 2: Removing Unwanted Characters

In a list of product codes, there are unnecessary underscores. You want to remove all underscores from the codes.

Example 3: Replacing Multiple Special Characters

Suppose you have a text string that contains special characters like !, @, #,$ and you want to replace all of them with a space.

Here’s the breakdown of the formula:

(1) SUBSTITUTE(A1, “!”, ” “)

• This innermost SUBSTITUTE function replaces all occurrences of the exclamation mark “!” in cell A1 with a space ” “. The result is a text string with exclamation marks replaced by spaces.

(2) SUBSTITUTE(result_1, “@”, ” “)

• The result from the previous step (result_1) is then further processed by the next SUBSTITUTE function. It replaces all occurrences of the at symbol “@” with a space ” “. This continues the process of replacing characters with spaces.

(3) SUBSTITUTE(result_2, “#”, ” “)

• The result from the previous step (result_2) is further processed to replace all occurrences of the hash symbol “#” with a space ” “.

(4) SUBSTITUTE(result_3, “$”, ” “)

• The result from the previous step (result_3) is once again processed to replace all occurrences of the dollar sign “$” with a space ” “.

(5) TRIM(result_4)

• Finally, the TRIM function is applied to the result from the last SUBSTITUTE operation (result_4). The TRIM function removes any leading or trailing spaces and ensures that there is only a single space between words.

The SUBSTITUTE function doesn’t support replacing multiple characters simultaneously using an array constant [like =SUBSTITUTE(A1, {“!”,”@”,”#”,”$”}, ” “)]

If you want to replace multiple special characters with a single SUBSTITUTE function, you would need to nest SUBSTITUTE functions as shown in the earlier examples.

Example 4: Replacing Specific Occurrences

You have a text string “A A A A A” and want to replace the fourth occurrence of “A” with “a”. In cell B1, input:

Conclusion

The Excel SUBSTITUTE function empowers you to manipulate text efficiently, giving you the ability to make precise replacements within text strings. By mastering this function, you open the door to a world of data cleaning, transformation, and consistency. With practice and creativity, you will uncover its potential to elevate your Excel skills to new heights.

Feel free to experiment, refine, and integrate SUBSTITUTE into your own projects. Excel is a toolbox, and SUBSTITUTE is a powerful tool that can help you achieve remarkable text transformations with ease.

Frequently Asked Questions (FAQs)

Q1: Can SUBSTITUTE handle case-insensitive replacements?

A1: No, SUBSTITUTE is case-sensitive. “Apple” and “apple” are treated differently.

Q2: What if old_text appears multiple times within text but I want to replace only the third occurrence?

A2: You can achieve this by specifying instance_num as 3 in your SUBSTITUTE formula.

Q3: Can I use cell references for old_text and new_text?

A3: Yes, you can use cell references as long as they contain the appropriate text values.

Q4: Does SUBSTITUTE work with numbers as well?

A4: Yes, SUBSTITUTE can replace numbers just like text, as long as you treat them as text entries.

Other Related Excel Functions

RIGHT Function

LEN Function

UPPER Function

LOWER Function

Join me on Instagram and YouTube for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!