Use Of Substitute Function In Excel
Use Of Substitute Function In Excel @BrainUpp
Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string
Excel SUBSTITUTE Function
How to use the Excel SUBSTITUTE function
Summary
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
Purpose
Replace text based on content
Return value
The processed text
Syntax
=SUBSTITUTE (text, old_text, new_text, [instance])
Arguments
text - The text to change.
old_text - The text to replace.
new_text - The text to replace with.
instance - [optional] The instance to replace. If not supplied, all instances are replaced.
Version
Excel 2003
Usage notes
The Excel SUBSTITUTE function can replace text by matching. Use the SUBSTITUTE function when you want to replace text based on matching, not position. Optionally, you can specify the instance of found text to replace (i.e. first instance, second instance, etc.).
SUBSTITUTE is case-sensitive. To replace one or more characters with nothing, enter an empty string ("").
Examples
Below are the formulas used in the example shown above:
=SUBSTITUTE(B5,"t","b") // replace all t's with b's
=SUBSTITUTE(B6,"t","b",1) // replace first t with b
=SUBSTITUTE(B7,"cat","dog") // replace cat with dog
=SUBSTITUTE(B8,"&","") // replace # with nothing
=SUBSTITUTE(B9,"-",", ") // replace hyphen with comma
The SUBSTITUTE function cannot replace more than one string at a time. However, SUBSTITUTE can be nested inside of itself to accomplish the same thing. For example, with the text "a (dog)" in cell A1, the formula below will strip parentheses () from text:
=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","") // returns "a dog"
This same approach can be used in a more complex formula to normalize telephone numbers.
Related functions
Use the REPLACE function to replace text at a known location in a text string. Use the SUBSTITUTE function to replace text by searching when the location is not known. Use FIND or SEARCH to determine the location of specific text.
Notes
SUBSTITUTE finds and replaces old_text with new_text in a text string.
Instance limits SUBSTITUTE replacement a particular instance of old_text.
When instance is omitted, all instances of old_text are replaced with new_text.
SUBSTITUTE is case-sensitive and does not support wildcards.
How to Use the SUBSTITUTE Function in Excel
Do you need to replace a specific part of a text? You can use the SUBSTITUTE function in Excel to help you. Here's how!
HOME
PRODUCTIVITY
How to Use the SUBSTITUTE Function in Excel
BY
AMIR M. BOHLOOLI
PUBLISHED JUL 28, 2021
Do you need to replace a specific part of a text? You can use the SUBSTITUTE function in Excel to help you. Here's how!
Microsoft Excel logo
In Excel, the SUBSTITUTE function finds a string in a cell and replaces it with another string. The SUBSTITUTE function is case-sensitive and looks for instances of the string in the cell.
You can choose whether the SUBSTITUTE function works on a specific instance of the string or if it affects all instances. This replaces, or substitutes, all occurrences of the old string with the new one.
SUBSTITUTE Function Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
text: Either a string itself or a target cell where you want the function to look.
old_text: The string that you want to replace.
new_text: The new string which will take the place of the old one.
[instance_num]: The instance number of the old string that you want to replace. Leave blank if you want all instances to be replaced.
Even though Excel's official syntax uses the term text rather than string, you can use the function on both numbers and symbols. If you want to remove a character, you can simply substitute it with blank.
How to Use the SUBSTITUTE Function
Nothing better than an example to see the SUBSTITUTE function in use. In this particular example, we have a phone number that has the wrong country code (+98). The goal is to change this country code to the correct one (+1) using the SUBSTITUTE function.
Select the cell that you want the substituted string to appear. That would be A2 for this example.
In the formula bar, enter the formula below and press Enter:
=SUBSTITUTE(A2, "98", "1", 1)
This will look inside cell A2, find the string 98, and replace it with 1. The instance number of 1 in the final part of the formula expresses that only the first 98 should be replaced.
Excel will now change the first 98 to a 1, changing the country code for the phone number. You can go ahead and remove the instance number and observe how the number will change, as every 98 is replaced with 1.
Watch video Use Of Substitute Function In Excel online without registration, duration hours minute second in high quality. This video was added by user Brain Up 11 September 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 19,97 once and liked it 47 people.