Pinterest Pixel

SUBSTITUTE Function – Replace Characters in Excel!

One of the most common tasks in Excel is replacing specific characters within a cell’s content. Imagine... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

SUBSTITUTE Function - Replace Characters in Excel! | MyExcelOnline SUBSTITUTE Function - Replace Characters in Excel! | MyExcelOnline

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:

See also  Index Match 2 Criteria with Data Validation

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

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

SUBSTITUTE Function - Replace Characters in Excel!

After pressing Enter, the cell will display:

SUBSTITUTE Function - Replace Characters in Excel!

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:
SUBSTITUTE Function - Replace Characters in Excel!

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)

SUBSTITUTE Function - Replace Characters in Excel!

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’.

SUBSTITUTE Function - Replace Characters in Excel!

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.

See also  Get the Day Name for a Date in Microsoft Excel - 3 Easy Ways

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”)

SUBSTITUTE Function - Replace Characters in Excel!

SUBSTITUTE Function - Replace Characters in Excel!

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.

SUBSTITUTE Function - Replace Characters in Excel!

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”.

SUBSTITUTE Function - Replace Characters in Excel!

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

SUBSTITUTE Function - Replace Characters in Excel!

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

SUBSTITUTE Function - Replace Characters in Excel!

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.

See also  How to Use Curved Arrows in PowerPoint Easily

SUBSTITUTE Function - Replace Characters in Excel!

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, “/”, “-“)

SUBSTITUTE Function - Replace Characters in Excel!

SUBSTITUTE Function - Replace Characters in Excel!

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 Function - Replace Characters in Excel!

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

SUBSTITUTE Function - Replace Characters in Excel!

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!

If you like this Excel tip, please share it
SUBSTITUTE Function - Replace Characters in Excel! | MyExcelOnline SUBSTITUTE Function - Replace Characters in Excel! | MyExcelOnline
See also  Excel Subtotal Function - Avoid Double Counting
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...