SUBSTITUTE Excel Formula


Home >> How to Use Excel Tutorial >> Excel Formulas List >> SUBSTITUTE Excel Formula



In this tutorial, you will learn how to use SUBSTITUTE in excel. SUBSTITUTE excel formula is useful to change every instance of a part of a text as you desire.


Why do We Need to Learn SUBSTITUTE in Excel?


In our data processing on a spreadsheet, sometimes there is a need to change the part of a cell content with another thing. This sometimes happens if there is an update that needs to be done in the content or if there is a mistake while doing entry to the cell in the spreadsheet so we need to do a fixing process.

For the illustration of the condition, for example we need to use a template for an invoice document that is in a spreadsheet media with some text paragraphs in it on a cell. In that invoice template, there might be some elements in each of the paragraphs that are marked with placeholders so the placeholders can be changed and adapted with the unique context of the document. For a clearer illustration, the placeholders of this invoice are for the name of the product or other details of the product that you want to make an invoice for. The elements to be changed can be spread in many positions in all of the paragraphs. Changing the elements one by one will, of course, take a lot of time and it will be troublesome if we do the process manually.

To do this fast, then we can use the SUBSTITUTE excel formula. SUBSTITUTE in excel will help you to change every part of a text based on your needs with the content that you want. It is very useful to be understood and learned if you need to fix or update part of the data in a spreadsheet or if you need to deal with a document template with some of its parts need to be adapted to your unique document context.


What is the SUBSTITUTE Excel Formula?


SUBSTITUTE excel formula is a formula with the usage objective to change a part of a text that might be repeated in it with another content as needed.

SUBSTITUTE in excel is a little bit different in its change process nature from the REPLACE formula where REPLACE is more in the direction of changing a part of a text based on its position in the text. SUBSTITUTE excel formula has more of a function to change every instance of an element in a text except if you choose the option to change just one of the instances and not all.

Generally, this is the explanation of the input needs for SUBSTITUTE in excel:


=SUBSTITUTE(text, old_text, new_text, instance_num)


Notes:
  • text = the text that you want to change the part of
  • old_text = part to be changed
  • new_text = substituting element
  • instance_num = optional. In which instance of the repeat of the part in the text that you want to change



How to Use SUBSTITUTE in Excel?


The following part will explain how to write a SUBSTITUTE excel formula. SUBSTITUTE in excel needs three required inputs which are the text which part wants to be changed, the part to be changed, and the new content for the part and also one optional input which is in which instance of the text part that you want the substitution to happen.


Using the SUBSTITUTE Excel Formula

  1. Type equal sign ( = ) in the cell where you want to put the result in

    SUBSTITUTE Excel Formula - Screenshot of Step 1

  2. Type SUBSTITUTE (can be with large and small letters) and open bracket sign after =

    SUBSTITUTE Excel Formula - Screenshot of Step 2

  3. Input the text or cell coordinate where the text to be changed its part is in after open bracket sign then type a comma sign ( , )

    SUBSTITUTE Excel Formula - Screenshot of Step 3

  4. Input the part that you want to change or cell coordinate where the writing of the part is in then type a comma sign

    SUBSTITUTE Excel Formula - Screenshot of Step 4

  5. Input the substituting element or cell coordinate where it is in

    SUBSTITUTE Excel Formula - Screenshot of Step 5

  6. Optional: Type a comma sign then input the number that represents the instance/in which repeat of the part of the text that you want to change or cell coordinate where that number is in

    SUBSTITUTE Excel Formula - Screenshot of Step 6

  7. Type close bracket sign

    SUBSTITUTE Excel Formula - Screenshot of Step 7

  8. Press Enter
  9. The process is done!

    Result with the optional number that represents the instance

    SUBSTITUTE Excel Formula - Screenshot of Step 9-1

    Result without the optional number that represents the instance

    SUBSTITUTE Excel Formula - Screenshot of Step 9-2



Exercise


After you have learned how to write SUBSTITUTE in excel, now is the time you deepen your understanding by doing the following exercise!

Download the exercise file and do all the instructions! Download the answer key file if you have done the exercise and want to check your answers or if you are confused and want some clues on how to do it!

Link for the exercise file:
Download here

Instructions

  1. Replace “a” letters in the left code with “c”!
  2. Replace all the numbers of “1” in the left code with “0”!
  3. Replace the second “x” letter in the left code with “z”!

Link to download the answer key file:
Download here


Additional Note

  • SUBSTITUTE in excel is case sensitive for the element that you want to change
  • SUBSTITUTE excel formula can be used also to remove all the instances of an element in the text that you want. The way to do this is to put two quotation signs (“”) when you need to input the substituting element in the formula

    SUBSTITUTE Excel Formula - Screenshot of Additional Notes 2-1

    SUBSTITUTE Excel Formula - Screenshot of Additional Notes 2-2





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert