SUBSTITUTE in Excel – Shiksha Online

SUBSTITUTE in Excel – Shiksha Online

3 mins read412 Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:36 IST

The article covers the SUBSTITUTE function in Excel. Learn how to implement the SUBSTITUTE formula through examples.

2022_06_substitute-in-Excel-1.jpg

The SUBSTITUTE function is among the most popular functions of Excel. This function finds a string in a cell and replaces it with another string. 

Content

SUBSTITUTE Formula in Excel

The syntax of the SUBSTITUTE function is:

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

Explore MS Excel Tutorials

The replace function takes four arguments –

text: The text where the substitution will be made; this includes the cell with text 

old_text: The existing text that needs to be replaced by the substring 

new_text: Sub text string that will replace the original text 

instance_num: The instance or occurrence number of the original text to be substituted

You can insert the SUBSTITUTE function as a part of any other formula in a cell as a worksheet function, allowing you to substitute the text in strings easily. You also get the flexibility to decide which part of the string you want to replace. An alternative is to use the Find and Replace Excel feature.

Power Query in Excel – Shiksha Online
Power Query in Excel – Shiksha Online
Power Query is a pretty powerful tool that allows you to import and clean millions of rows into a data model. Learn how to use Power Query in Excel through...read more
Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS...read more
How to Create MIS Report in Excel?
How to Create MIS Report in Excel?
A Management Information System (MIS) report in Excel is a vital tool for businesses and organizations, offering valuable insights by consolidating and presenting data in an organized and comprehensible format....read more

Example – SUBSTITUTE in Excel

Let’s check out some exciting examples of the use of the SUBSTITUTE function in Excel – 

Must Explore – MS Excel Courses

Substitute a Part in String

In the below example, we have substituted the word “Trek” from the string “Star Trek” with “Wars,” making it “Star Wars.

The formula used is –

=SUBSTITUTE(B6,”Trek”,”Wars”)

Replace with Nothing

In the above example, we have a string with digits and alphabets. We will apply the formula =SUBSTITUTE(B6, “3476”,””)

The formula selects the cell with string, the data to replace, and “” without any spaces. This formula will substitute the digits with nothing, leaving behind just the alphabet’s string.

Tutorial – XLOOKUP in Excel – Shiksha Online
Tutorial – XLOOKUP in Excel – Shiksha Online
The article talks about an advanced and flexible lookup function, XLOOKUP.
How To Calculate Percentage In Excel
How To Calculate Percentage In Excel
Percentages are a very powerful tool for data analysis. They can be used to make data more concise, easier to understand, and more comparable. In this tutorial, we will learn...read more
How To Create A Pivot Table In Excel?
How To Create A Pivot Table In Excel?
A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data. It is essentially a way of reorganizing and manipulating data...read more

Nested Substitutions

You can not substitute a bunch of strings using the SUBSTITUTE function in a single cell. However, Excel gives you the flexibility to apply three separate substitutions performed on the same cell. You can nest the functions inside each other. Let’s understand how you can do this –

We have a sample string that has the value “A red apple“. We want to apply the SUBSTITUTE function to substitute its content and get a new text string that suggests “An orange carrot“.

Here we have to replace the entire string, which has three instances to substitute. We will use the formula –

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,B7,C7),B8,C8),B9,C9)

Where,

B4=Old value

B7 & C7= First values to substitute

B8 & C8= Second values to substitute

B9 & C9= Third values to substitute

Here, B7 is the old value that is being replaced by the new value in C7. Hence, they are placed next to each other when we apply the formula. This process goes on.

Excel REPLACE vs. Excel SUBSTITUTE

REPLACE and SUBSTITUTE functions in Excel are pretty similar; they help replace the strings. However, there are some fundamental differences between the two, which include – 

  • SUBSTITUTE replaces one or more instances of a given character or a text string. You can use the SUBSTITUTE function if you know which text to replace.
  • REPLACE changes characters in a given position of a text string. You should use the Replace function if you know the position of the character(s) that you have to swap.
  • The SUBSTITUTE function allows the addition of an optional parameter (instance_num). This parameter specifies which occurrence of old_value needs to change to new_value. 

Points to Remember

  • SUBSTITUTE is case-sensitive
  • You can use the SUBSTITUTE function with its hardcoded values and with cell references
  • The SUBSTITUTE function looks for instances of the string in the cell

With the help of the SUBSTITUTE function, you can easily replace strings with blanks or replace blanks with strings. You can also use the Find and Replace Search feature in Excel to find and replace things. Coming up in the next blog.


Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments