Excel SUBSTITUTE Function: Formula, Examples, How to Use

The Excel SUBSTITUTE function is a powerful tool for data manipulation, allowing you to quickly and easily replace text within cells. With this function, you can save time and improve your data accuracy.

In this article, we'll take a detailed look at how to use the Excel SUBSTITUTE, with examples and a step-by-step guide. Whether you're a beginner or an advanced Excel user, this article will help you master the art of data manipulation.

By the end of this article, you'll have a solid understanding of how to use the SUBSTITUTE function to replace text in your Excel spreadsheets. So, let's dive in and start manipulating data like a pro!

What is a SUBSTITUTE function in Excel?

The SUBSTITUTE function is a useful tool in Excel that helps you replace specific text or characters in a cell with new ones. This can be helpful when you have a large amount of data that needs to be edited quickly and accurately.

The syntax of the SUBSTITUTE function in Excel is as follows: =SUBSTITUTE(text, old_text, new_text, [instance_num]). The "text" argument refers to the cell or range of cells that you want to replace text in, "old_text" is the text or character(s) that you want to replace, and "new_text" is the text or character(s) that you want to replace the old text with.

The "instance_num" argument is optional and specifies which occurrence of the old text you want to replace.

For example:

Let's say you have a column of data with the word "apple" in each cell, but you want to replace all instances of "apple" with "orange." You can use the SUBSTITUTE function to quickly make this change by entering the formula =SUBSTITUTE(A1,"apple","orange") in the adjacent column, where A1 is the first cell containing the text "apple".

How to Use the SUBSTITUTE Function

The SUBSTITUTE function in Excel is a handy tool for replacing specific text or characters in a cell or range of cells. Here's an example to help explain how to use the SUBSTITUTE function:

Here's another example to show how the SUBSTITUTE function can be used in Excel:

Let's say you have a list of email addresses ending with "@oldcompany.com" and you want to replace it with the new company domain "@newcompany.com". 

Here's how you can use the SUBSTITUTE function to do it:

In this example, we have a list of email addresses in the left column that all end with "@oldcompany.com". The SUBSTITUTE function was used in the right column to replace "@oldcompany.com" with "@newcompany.com".

The formula used in the right column is =SUBSTITUTE(A2, "@oldcompany.com", "@newcompany.com", 1)

This formula tells Excel to look inside cell A2, find the text string "@oldcompany.com" and replace it with "@newcompany.com". The final argument in the formula, the number 1, tells Excel to replace only the first occurrence of "@oldcompany.com".

Using the SUBSTITUTE function in Excel can save you a lot of time when you need to make changes to large amounts of data.

Nested Substitutions

The SUBSTITUTE function in Excel can be nested inside other SUBSTITUTE functions to replace multiple instances of text in a single cell. This means you can use multiple nested SUBSTITUTE functions to replace more than one string in a single cell.

For example:

Let's say you have a list of products that contain the word "regular" and you want to replace it with the word "standard" and the word "large" with the word "extra-large". Here's how you can use nested SUBSTITUTE functions to do it:

In this example, we have a list of product names in the left column. The SUBSTITUTE function was used in the right column to replace "regular" with "standard" and "large" with "extra-large" using nested functions.

The formula used in the right column is =SUBSTITUTE(SUBSTITUTE(A2, "Regular", "Standard"), "Large", "Extra-Large")

This formula tells Excel to look inside cell A2, find the text string "Regular" and replace it with "Standard," and then find the text string "Large" and replace it with "Extra-Large."

By nesting SUBSTITUTE functions inside each other, you can replace multiple strings in a single cell with just one formula.

Difference between SUBSTITUTE and REPLACE Functions

The SUBSTITUTE and REPLACE functions in Excel are used to replace text within a cell, but they work differently.

SUBSTITUTE is used when you want to replace specific instances of text within a cell. For example, if you want to replace all instances of the word "cat" with "dog" in a cell, you would use SUBSTITUTE. This function allows you to specify the text you want to replace and what you want to replace it with.

On the other hand, REPLACE is used when you want to replace text based on its position within a cell. For example, if you want to replace the first 3 characters of a cell with the word "hello," you would use REPLACE. 

This function allows you to specify the starting position of the text you want to replace, how many characters you want to replace, and what you want to replace them with.

Here's an example: 

To replace the last name of each person with a different name, we can use the SUBSTITUTE and REPLACE functions. Let's say we want to replace the last name "Doe" with "Smith" and the last name "Johnson" with "Williams".

To use SUBSTITUTE, we would enter the following formula in cell B1:

=SUBSTITUTE(A1, "Doe", "Smith")

This will replace "Doe" with "Smith" in cell A1, resulting in "John Smith". We can copy this formula down to cells B2 and B3 to replace the last names in the entire column.

To use REPLACE, we would enter the following formula in cell C1:

=REPLACE(A1, FIND(" ", A1)+1, LEN(A1)-FIND(" ", A1), "Williams")

This will find the position of the space between the first and last name in cell A1 using the FIND function and then use the REPLACE function to replace the last name (starting from the position after the space) with "Williams," resulting in "John Williams." We can copy this formula down to cells C2 and C3 to replace the last names in the entire column.

What are some tips and tricks for using the SUBSTITUTE function effectively and efficiently?

Here are some tips and tricks for using the SUBSTITUTE function in Excel:

1. Use the function for specific text replacement: The SUBSTITUTE function works best when you need to replace specific text in a cell. It can be less effective when you need to replace a range of text.

2. Combine with other functions: You can combine SUBSTITUTE with other functions like LEFT, RIGHT, and MID to extract or replace specific parts of a cell's content.

3. Nest the function: If you need to perform multiple substitutions on the same cell, you can nest the SUBSTITUTE function inside another SUBSTITUTE function.

4. Use the Find and Replace feature: If you have a large dataset, you can use Excel's Find and Replace feature to replace specific text across multiple cells or worksheets.

5. Use wildcards: You can use wildcards like the asterisk () or question mark (?) in the old_text argument to replace multiple variations of a text string. For example, if you want to replace all variations of the word "colour" with "color", you can enter "colour" in the old_text argument.

Final Thoughts

In conclusion, setting the alarm in Excel is easy and can be done in just a few steps. By following the simple instructions above, you can ensure that you never miss an important deadline or meeting again.

To elaborate further, Excel has a built-in feature called "Visual Basic for Applications" (VBA) which can be used to program an alarm. This can be done by writing a few lines of code that will trigger an alert or a sound at a specific time or when certain conditions are met.

By utilizing this feature, you can customize your alarm to meet your specific needs and make sure that you are always on top of your tasks. Overall, setting the alarm in Excel can be a useful tool to increase productivity and stay organized.

One more thing

We’re glad you’ve read this article up to here :) Thank you for reading.

If you have a second, please share this article on your socials; someone else may benefit too. 

Subscribe to our newsletter and be the first to read our future articles, reviews, and blog posts right in your email inbox. We also offer deals, promotions, and updates on our products and share them via email. You won’t miss one.