What is SUBSTITUTE in Excel Formula?
SUBSTITUTE in Excel is a function that you can use to change a particular word or text in a cell and replace it with a new text. For example, you can use the SUBSTITUTE function to replace “old” with “new” in the sentence: “My book is old.” The function will change the sentence to “My book is new.”
It takes the text you specify, looks for a match in the original text, finds all the places where the text is present, and then replaces all those instances with the new text. In case you don’t want to change all the instances of the text, you can also select the particular instance number you want to replace. It is an invaluable function when you need to clean and transform text-based data & standard formats and perform various data manipulation tasks.
Table of Contents
- What is SUBSTITUTE in Excel Formula?
- Syntax
- Arguments
- How to Use SUBSTITUTE in Excel? (with Examples)
- Things to Remember
Syntax
Arguments
- text: This is the cell reference or text string in which you want to perform the replacement
- old_text: This is the text you want to change
- new_text: The new text you want to add instead of the old_text
- [instance_num] (optional): It is the number indicating which particular occurrence you want to substitute. If not provided, the function will replace all occurrences.
How to Use SUBSTITUTE in Excel? (with Examples)
Example #1: Substituting a Single Value in a Cell
We want to replace a single word “seen” with “saw” in cell A1, which contains the text: “ I seen the movie last night”.
- Enter the formula =SUBSTITUTE(A1, “seen”, “saw”) in B1
- This formula takes the following arguments:
- A1: The cell containing the original text.
- “seen”: The text you want to replace.
- “saw”: The replacement text.
- Click Enter for the result.
Result:
Result:
Cell B1 will now display the result, where “P” is replaced with “Product” and “O” is replaced with “Orders”.
Example #3: Use Instance_num to Substitute Specific Occurrence
We want to replace the second occurrence of the word “happy” with “smiling” in the sentence “I was so happy to see her happy.”
- Identify the cell with the text, i.e., cell A1
- In cell B1, type the following formula: =SUBSTITUTE(A1, “happy”, “smiling”, 2)
- A1 is the reference to the cell containing the original sentence.
- “happy” is the text you want to replace.
- “smiling” is the text you want to replace it with.
- 2 specifies that you want to replace the second occurrence of the text.
- Press the Enter key
Result:
Cell B1 will now display the updated sentence: “I was so happy to see her smiling.”
Example #4: SUBSTITUTE Formula in Excel to Remove Spaces
We can use the SUBSTITUTE in Excel formula to remove spaces between the characters in a cell. Let us remove the extra spaces between the text “O M G” in cell A1.
- Enter the following formula in a cell B1: =SUBSTITUTE(A1, ” “, “”)
- Press Enter
Result:
Cell B1 will now display the text “OMG” without spaces.
Example #5: Formatting Dates Within a Text String
We have to replace the year “1996” with “2006” in cell A1, where the cell contains the date “19/11/1996”.
- In cell B1, enter the following formula: =SUBSTITUTE(A1, “1996”, “2006”)
- Press Enter.
Result:
Cell B1 will display the result with the year “2006” replacing “1996” from cell A1.
Example #6: Nested SUBSTITUTE Function
Imagine you work for a company that manages a customer database with customer information, including phone numbers. Different employees entered phone numbers in various formats, leading to inconsistencies. Your task is to clean and standardize these phone numbers using Excel’s SUBSTITUTE function.
Step 1: Before Cleaning
The phone numbers in column B contain unnecessary characters such as: (, -, ., )
Step 2: Applying SUBSTITUTE Function
To remove unwanted characters, add the below formula in Cell C2 and then drag it to apply in other cells.
Step 3: After Cleaning
The SUBSTITUTE function helps clean up the data, making the phone numbers look neat without unnecessary characters.
Things to Remember While Using SUBSTITUTE Function
- Enclose text values within double quotes. For instance, sweet and salty should be added in double quotes like this: =SUBSTITUTE(A1, “sweet”, “salty”)
- The SUBSTITUTE function doesn’t modify the original text directly. It adds the result in a new cell. For example, if Cell A1 has the string “Excel Function” and we type the formula =SUBSTITUTE(A1, “Function”, “Formula”) in Cell B1. The SUBSTITUTE function will add the new “Excel Formula” text to Cell B1 without changing the original “Excel Function” text in Cell A1
- If the function does not find the specified text within the old_text, it returns the original text as it is. For instance, if you use the formula =SUBSTITUTE(A1, “Paris”, “Italy”), and cell A1 contains just “Rome”, the result will stay as “Rome” because “Paris” is not part of the text in cell A1
- The SUBSTITUTE function is case-sensitive, meaning distinguishing between uppercase and lowercase letters is important. For instance, Cell A1 contains the text “new york”. Using the formula =SUBSTITUTE(A1, “New”, “jersey”) won’t find a match for the uppercase “New”. Thus, no substitution will take place, and the output will remain “new york”
- In Excel, wildcards (*, ? and ~) have a special role, and we can use them within functions to simplify operations. However, the SUBSTITUTE function considers the wildcard characters as normal characters.
Frequently Asked Questions (FAQs)
Q1. What are some related functions to the SUBSTITUTE function in Excel?
A few other functions that work similarly to the SUBSTITUTE in Excel are text manipulation functions like REPLACE, FIND, and SEARCH. The FIND and SEARCH functions locate the position of the specific text within a larger text string. On the other hand, the REPLACE function replaces text at a known position within a text string.
Q2. Difference between SUBSTITUTE and REPLACE Functions
Answer: The SUBSTITUTE function replaces occurrences of a specific substring with another substring within a given text string. Here, we specify the substring we want to replace, and the function targets all instances of the specified substring. On the other hand, the REPLACE function replaces a specific portion of a text string with another substring. Here, we need to select the starting position and the number of characters to be replaced.
Q3. Is the substitute function available in all Excel versions as well as Mac?
Answer: The SUBSTITUTE function is widely available across various Excel versions, ensuring seamless text manipulation for users. It is present in Excel 2003, 2007, 2010, 2013, 2016, 2019, and Excel 365. Mac, iOS, and Android users can also utilize this function in their respective Excel environments. Its consistent presence and possible minor enhancements in newer versions make it a dependable tool for text manipulation tasks.
Recommended Articles
This article on the SUBSTITUTE in Excel formula explains the various ways to use the SUBSTITUTE function in Excel. You can find easy-to-understand, stepwise examples along with a downloadable Excel template. For more related information, check the following recommendations.
