Hello friends, in this post we have written about Replace and Substitute Function, we have told you about these two functions by taking some good examples.
-: Definition of Replace Function :-
Replace function is text function in excel, which replaces characters specified by location in a given text string with another text string.
Syntax: =REPLACE(old_text,start_num,num_chars,new_text)
Arguments
-
Old_text - The text in which you want to do replacing task.
-
Start_num - Which number character of the text string do you want to replace with another text string.
Num_chars - How many number of character you want to replace of text string.
-
New_text - The text to replace old_text with.
Example 1.
=REPLACE("https://www.Yahoo.com",13,5,"Google") ↣ Return value https://www.Google.com
Example 2.
=REPLACE("https://www.Google.com",1,8,"") ↣ Return value www.Google.com
-: Lets Explain Repalce Function or Formula in Excel Table :-
| A | B | C | |
|---|---|---|---|
| Old Text | Formula Text | Result | |
| 1 | https://www.Yahoo.com | =REPLACE("https://www.Yahoo.com",12,5,"Google") | https://www.Google.com |
| 2 | https://www.Google.com | =REPLACE("https://www.Google.com",1,8,"") | www.Google.com |
-: Definition of Substitute Function :-
The Excel SUBSTITUTE is a text function, this function replaces text in a given string by matching.
the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
Syntax: =Substitute(Text,Old_Text,new_text,[instance])
Arguments
-
Text - The text in which you want to change.
-
Old_Text - The text which you to replace.
new_text - The text to replace with.
-
[instance] - [optional] The instance to replace. If not supplied, all instances are replaced.
Example
=SUBSTITUTE("993-021-5612","-","") ↣ Return value "9930215612";
-: Lets Explain Substitute Function or Formula in Excel Table :-
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Input Text | Formula | Output | Note |
| 2 | TOMATO | =SUBSTITUTE(A2,"T","Z") | ZOMAZO | All instances Replaced |
| 3 | TOMATO | =SUBSTITUTE(A3,"T","Z",1) | ZOMATO | First Instance Only Replaced |
| 4 | Tomato | =SUBSTITUTE(A4,"T","z") | zomato | Substitute is Case Senstive |
| 5 | %Tomato%% | =SUBSTITUTE(A5,"%","") | Tomato | Replace with Nothing |
| 6 | Mi,CSK,GT,LSG | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6, "Mi", "Mumbai Indians"), "CSK", " Chennai Super King"), "GT", " Gujarat Titans"),"LSG"," Lucknow Super Gaints") | Mumbai Indians, Chennai Super King, Gujarat Titans, Lucknow Super Gaints | Nested Substitute |
We are sure that after reading this post, you will be able to increase your Excel knowledge. I must have been interested, we will keep posting such knowledge related posts for you, just like this you guys keep giving us your love.
Thank You.
By kamalesh Kumar Bind
No comments:
Post a Comment