SUBSTITUTE Function – Replace Characters in Excel!

One of the most common tasks in Excel is replacing specific characters within a cell’s content. Imagine you’re working on a dataset that includes many products with spelling errors or inconsistencies. The SUBSTITUTE Function in Excel can be your life-saver! In this article, we’ll explore how to Replace Characters in Excel with the SUBSTITUTE Function, its’ comparison with the REPLACE Function, and some helpful examples –

Download the Excel Workbook below to follow along and understand how to Replace Characters in Excel with the SUBSTITUTE Function –

download excel workbookReplaceCharacterswithSubstitute.xlsx

Syntax and Arguments

The SUBSTITUTE function has the following syntax:

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

text: This is the text string you want to modify.
old_text: This is the text you want to replace.
new_text: This is the new text that will replace the old text.

instance_num (optional): It specifies which occurrence of old_text to replace. If omitted, all occurrences are replaced.

Basic Usage of SUBSTITUTE

Let’s say you want to substitute the space character with “-“ in “High tech” To do this, follow these steps:

Click on the cell where you want to place the modified text.

In the cell or formula bar, enter: =SUBSTITUTE(A2,” “,”-“)

After pressing Enter, the cell will display:

You will be presented with the new data. It contains the character “-“ instead of the space.

Note: The SUBSTITUTE function is case-sensitive. To make it case-insensitive, you might need to use other functions like LOWER or UPPER for consistent replacements.

Replacing Multiple Instances

By default, SUBSTITUTE replaces all occurrences of `old_text` within `text`. As displayed here:

If you want to replace only a specific instance, you can use the `instance_num` argument.

For instance, if you want to replace the first occurrence of the space ” ” with “-,” you would modify the formula like this:

=SUBSTITUTE(A2, ” “, “-“, 1)

As you can see, the SUBSTITUTE function replaced the first space, and not the second one. This is because we indicated 1 for the “instance_num’.

The Difference Between SUBSTITUTE and REPLACE

If you’re looking to replace a specific text or number at a known position within your data, then the REPLACE function is your ideal choice. On the other hand, if the text or number you wish to replace appears multiple times throughout your data, the SUBSTITUTE function is the more suitable option.

Click here to learn how to Clean Data with Excel’s REPLACE Formula!

Let’s look at these steps to illustrate the differences better:

Suppose we want to replace the word “right” from the phrase “As right as rain”, to “wrong”.

This is how we would do it with the REPLACE function:
=REPLACE(A2,4,5,”wrong”)

If we drag the formula down, you will notice that it replaced the word “RIGHT” even though it was capitalized. That’s because the REPLACE function is not case-sensitive.

If we drag the formula down further, you will notice that it will not replace the second “right” because we only specified for it to replace the first “right”.

To use the SUBSTITUTE formula, enter =SUBSTITUTE(A7,”right”,”wrong”)

If we drag the the formula down a row, you’ll notice that the SUBSTITUTE formula replace both words.

If we drag the formula down once more, you will notice that the SUBSTITUTE formula did not replace the capitalized word. Demonstrating that the formula is case-sensitive.

Practical Examples

Let’s explore a few practical examples of how SUBSTITUTE can be used:

Example 1 – Reformatting Dates:
Suppose you have a column of dates in the format “MM/DD/YYYY,” and you want to change it to “YYYY-MM-DD.” You can use SUBSTITUTE as follows:

=SUBSTITUTE(A2, “/”, “-“)

Example 2 – Correcting Misspellings:
If you have a column with product names and some of them contain misspellings like “aquired” instead of “acquired,” you can correct them with SUBSTITUTE:

=SUBSTITUTE(A6, “aquired”, “acquired”)

Click here to learn how to Add A Comma in Excel between Names with SUBSTITUTE Formula

Conclusion
There you have it! By mastering the usage of SUBSTITUTE and knowing the difference between it and the REPLACE function, you can efficiently transform and format your data according to your specific needs.

Click here to access Microsoft’s tutorial on How to Use the SUBSTITUTE Function!

Click here to learn the 9 Tips for Formatting Excel Spreadsheets Like a Pro!