Joshua earned an MBA from USF and he writes mostly about software and technology.
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:
- Click into a cell and Type "=SUBSTITUTE(".
- After the open parenthesis, select the cell where changes are to be made
- Add the text that is to be replaced (old text)
- Add the replacement text (new text)
- 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.
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.
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.
The result is displayed below. The formula can also be typed out easy just as it displayed in the formula bar below.
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.
Microsoft. (n.d.). Substitute function. SUBSTITUTE function. https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332.
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