Skip to main content

How to Use the SUBSTITUTE Function in Excel

Joshua earned an MBA from USF and he writes mostly about software and technology.

The SUBSTITUTE function is shown in use   here in the above illustration. The result of the function will be that every instance of "Sales" that appears in cell A2 will be changed to "Cost".

The SUBSTITUTE function is shown in use here in the above illustration. The result of the function will be that every instance of "Sales" that appears in cell A2 will be changed to "Cost".

The Purpose of the SUBSTITUTE Function

The SUBSTITUTE function is a powerful tool replaces a text character or text characters with an alternate text character in a string. In other words, this function replaces text by matching. Depending on your goals, you can replace all of a certain character or specific characters in the string.

The SUBSTITUTION Function Syntax

The SUBSTITUTE function syntax is very specific and must be entered manually like a formula to work unless the function is inserted. The steps for typing this function manually can be followed below:

  1. Click into a cell and Type "=SUBSTITUTE(".
  2. After the open parenthesis, select the cell where changes are to be made
  3. Add the text that is to be replaced (old text)
  4. Add the replacement text (new text)
  5. Add the instance number (optional)

Below in bold is an example the correct syntax to use for the SUBSTITUTION function. Additionally, each argument of the function is summarized.

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

  • Text: This argument is a requirement of the function and can be a reference to a cell or text where a substitution needs to be made to one or more characters in that text.
  • Old_text: This requirement is also a requirement. The old text is characters or the text that the function will replace.
  • New_text: Also a requirement, the new text is the text that will replace the older unwanted characters are text.
  • Instance_num: This is the optional part of the function and used if you need to specify the exact occurrence of the older text that must be replaced. You have an option to leave this blank so that all occurrences of the older text will be changed to the newly selected text. Otherwise, when it is specified, only that text will be replaced.

Simplified SUBSTITUTION Function Example

There is some text in an Excel worksheet cell that contains 1s and 0s. I would like to use the SUBSTITUTION function to substitute the second occurrence of zero with a 2.

Inserting the SUBSTITUTE Function

The SUBSTITUTE function can be inserted into with the use of an insert tool for functions. Select a cell where the function is to display results to get started.

Next, select the formulas tab followed by selecting the "Text" option button on the ribbon. Choose SUBSTITUTION from the list of options.

To insert the SUBSTITUTE function from the formula tab you must first navigate to: Formulas→Text→SUBSTITUTE

To insert the SUBSTITUTE function from the formula tab you must first navigate to: Formulas→Text→SUBSTITUTE

After the the functions arguments window appears, a cell reference can either be typed in or selected from the worksheet by clicking on the up arrow to the right of the text field. Let's say cell B5 contains 100001 and I want to replace the second 0 with a 1. B1 would be entered into the text field.

Next the old text 0 would be added followed by the new text 1. Since I only want the second 0 to be replaced the instance would be 2.

Scroll to Continue
The functional arguments window is where each function argument is entered when inserting a function. Notice that tips are given depending on which arguments text box the cursor is in.

The functional arguments window is where each function argument is entered when inserting a function. Notice that tips are given depending on which arguments text box the cursor is in.

The illustration below shows the insert method with the necessary data populated. It also shows what the result will be. When the OK button is clicked the function is executed and the 0 is replaced with a 1.

how-to-use-the-substitute-function-in-excel

The result is displayed below. The formula can also be typed out easy just as it displayed in the formula bar below.

how-to-use-the-substitute-function-in-excel

To learn more about using functions in Excel I recommend The Excel 2019 Bible book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

References

Microsoft. (n.d.). Substitute function. SUBSTITUTE function. https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332.

How to Use the COUNT Function in Excel

How to Use the AVERAGE Function in Excel

How to Use the ABS Function in Excel

How to Remove Errors in Excel With the IFERROR Function

How to Use the VLOOKUP Function in Excel

Converting Measurement Units: Using the CONVERT Function

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder

Related Articles