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: Below in bold is an example of 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 is used if you need to specify the exact occurrence of the older text that must be replaced. You have the 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 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 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 easily just as it is displayed in the formula bar below. 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
