Welcome to Excel Avon
How to use SUBSTITUTE Formula in Excel
In this post I Will Explain SUBSTITUTE FORMULA .Replace text based on content or The Excel SUBSTITUTE formula replaces text in a given string by matching.
Parameter
Argument-
text- The text to change.
Old_text – The Text to replace.
New_text – The text we want to replace Old_text with.
Instance_num – The instance to replace. If not supplied, All instances are replace.
Note :
- When instance is omitted, all instances of old_text are replace with new_text.
- SUBSTITUTE is case-sensitive and does not support.
- Instance limits SUBSTITUTE replacement a particular instance of old_text.
How to use SUBSTITUTE Formula in Excel
The Excel SUBSTITUTE formula can replace text by matching. Use the SUBSTITUTE formula when you want to replace text based on matching, not position. Optionally, you can specify the instance of found text to replace.
Example 1
I will use formula without instance No. so here I will use this formula =SUBSTITUTE((text, old_text, new_text)
After using formula
I will write in C5 =SUBSTITUTE(C1,C2,C3) without 4th parameter.
Result
Example 2
In this example I will show you SUBSTITUTE formula with 4th parameter so I will write here.
=SUBSTITUTE (text, old_text, new_text, [instance])
Now I will use formula:
I will write in C5 =SUBSTITUTE(C1,C2,C3,C4) with 4th parameter.
So now we get result.
By this way you can use SUBSTITUTE formula with instance and without instance also.
You can also check my well explained video about SUBSTITUTE formula
