SUBSTITUTE
The SUBSTITUTE function is used to replace occurrences of a specific substring within a text string with a new substring. It's handy for making text substitutions or modifications in Excel.
Syntax 🔗
=SUBSTITUTE(text, old_text, new_text, [instance_num])
text | The original text string where substitutions will be made. |
old_text | The text to be replaced in the original text string. |
new_text | The text that will replace the old_text. |
instance_num (Optional) | The occurrence number of old_text to be substituted. If omitted, all occurrences will be replaced. |
About SUBSTITUTE 🔗
When you find yourself needing to switch out specific words, phrases, or characters within a text string in Excel, the SUBSTITUTE function is your go-to tool. It comes in handy for altering text by replacing occurrences of a particular substring with a new one. This function is particularly useful when you need to perform multiple replacements efficiently within a large body of text data, saving you time and effort in editing tasks across your spreadsheet projects. By specifying the original text, the target to replace, the replacement text, and optionally, the specific occurrence to substitute, you can swiftly make precise amendments to your text strings with ease.
Examples 🔗
Let's say you have the text 'apple,banana,orange,banana,apple' in cell A1 and you want to replace all occurrences of 'banana' with 'grape'. The SUBSTITUTE formula would be: =SUBSTITUTE(A1, 'banana', 'grape')
Suppose you have the text 'I love pizza, pizza is delicious!' in cell A1 and you want to replace the second occurrence of 'pizza' with 'sushi'. The SUBSTITUTE formula would be: =SUBSTITUTE(A1, 'pizza', 'sushi', 2)
Notes 🔗
The SUBSTITUTE function is not case-sensitive, meaning it doesn't differentiate between uppercase and lowercase letters when making substitutions. Also, keep in mind that specifying the instance_num argument allows you to replace only a specific instance of the old_text within the original text, offering control over the substitution process.
Questions 🔗
No, the SUBSTITUTE function is not case-sensitive. It treats all letters as equal, regardless of their case, when identifying and replacing old_text within the original text.
Can I replace specific instances of text using the SUBSTITUTE function?Yes, you can replace specific instances of text by specifying the instance_num argument in the SUBSTITUTE function. This allows you to target and replace only particular occurrences of the old_text within the text string.
Yes, you can nest SUBSTITUTE functions within each other in Excel. This technique can be useful for performing multiple sequential text replacements, creating a chain of substitutions within a single formula.
What happens if the old_text specified in SUBSTITUTE is not found in the original text?If the old_text specified in the SUBSTITUTE function is not found within the original text, the function will return the original text string unaltered, without making any replacements.