SUBSTITUTE in Excel – Shiksha Online

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

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
• Example – SUBSTITUTE in Excel
  • Substitute a Part in String
  • Replace with Nothing
  • Nested Substitutions
• Excel REPLACE vs. Excel SUBSTITUTE
• Points to Remember

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.

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.

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