✨ Part 1: Introduce
💡 Definition: The SUBSTITUTE function in Microsoft Excel is a text function that replaces a specific occurrence of a text string within another text string with a new text string.
🎯 Purpose: The purpose of the SUBSTITUTE function is to modify text by replacing a specified substring with another. It is commonly used for Excel’s text manipulation, data cleansing, and formatting tasks.
📚 Syntax & Arguments: The syntax of the SUBSTITUTE function is as follows:
=SUBSTITUTE(text, old_text, new_text, [instance_num]) text: This is the required argument representing the text string or cell reference containing the original text you want to modify.old_text: This is the required argument that describes the specific substring withintextthat you want to replace.new_text: This is the argument necessary representing the new substring you want to replaceold_text.[instance_num]: This is an optional argument that specifies the occurrence ofold_textto replace. If omitted, all occurrences are replaced.
💡 Return Value: The SUBSTITUTE function returns a new text string with the specified occurrences old_text replaced by new_text.
📝 Remarks:
- The SUBSTITUTE function is case-sensitive. It considers uppercase and lowercase characters as distinct.
- By default, the SUBSTITUTE function replaces all occurrences
old_textwithintext. If you want to replace a specific event, you can use the[instance_num]argument to specify it. - The SUBSTITUTE function does not modify the original text string. It returns a modified version of the text.
✨ Part 2: Examples
Let’s explore three examples that demonstrate the usage of the SUBSTITUTE function:
1️⃣ Example 1: Replacing Text in Product Names
| A | B | |
|---|---|---|
| 1 | Product Name | Modified Product Name |
| 2 | Widget ABC | =SUBSTITUTE(A2,”ABC”,”XYZ”) |
| 3 | XYZ-123 | =SUBSTITUTE(A3,”-“,”_”) |
| 4 | Apple Green | =SUBSTITUTE(A4,”Green”,”Red”) |
In this example, we have product names in column A and want to replace specific substrings within them. The SUBSTITUTE function is used in column B to modify the terms.
- The formula
=SUBSTITUTE(A2,"ABC","XYZ")in cell B2 replaces the substring “ABC” in the product name “Widget ABC” with “XYZ”. - The formula
=SUBSTITUTE(A3,"-","_")in cell B3 replaces the hyphen “-” in the product name “XYZ-123” with an underscore “_”. - The formula
=SUBSTITUTE(A4,"Green","Red")in cell B4, replaces the substring “Green” in the product name “Apple Green” with “Red”.
2️⃣ Example 2: Cleaning Phone Numbers
| A | B | |
|---|---|---|
| 1 | Phone Number | Cleaned Phone Number |
| 2 | (123) 456-7890 | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,”(“,””),”)”,””),”-“,””) |
| 3 | +1 987 654 3210 | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,” “,””),”+”,””),””,””) |
In this example, we have phone numbers with different formatting or characters in column A. We want to clean up the phone numbers by removing unnecessary characters. The SUBSTITUTE function is nested in column B to achieve this.
- The formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")cell B2 removes the parentheses, spaces, and hyphens from the phone number “(123) 456-7890”. - The formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3," ",""),"+",""),"","")cell B3 removes the areas, plus sign, and any remaining characters from the phone number “+1 987 654 3210”.
3️⃣ Example 3: Replacing Multiple Occurrences
| A | B | |
|---|---|---|
| 1 | Description | Modified Description |
| 2 | Apples and oranges and apples | =SUBSTITUTE(A2,”apples”,”bananas”,2) |
| 3 | ABC ABC ABC ABC | =SUBSTITUTE(A3,”ABC”,”XYZ”,3) |
| 4 | 1234567890 1234567890 | =SUBSTITUTE(A4,”123″,”999″,1) |
In this example, we have descriptions in column A, and we want to replace specific substrings but only for certain occurrences. The SUBSTITUTE function is used in column B to achieve this.
- The formula
=SUBSTITUTE(A2,"apples","bananas",2)cell B2 replaces the second occurrence of “apples” with “bananas” in the description “Apples and oranges and apples”. - The formula
=SUBSTITUTE(A3,"ABC","XYZ",3)in cell B3 replaces the third occurrence of “ABC” with “XYZ” in the string “ABC ABC ABC ABC”. - The formula
=SUBSTITUTE(A4,"123","999",1)in cell B4 replaces the first occurrence of “123” with “999” in the number sequence “1234567890 1234567890”.
4️⃣ Example 4: Formatting URLs
| A | B | |
|---|---|---|
| 1 | URL | Formatted URL |
| 2 | https://www.example.com | =SUBSTITUTE(SUBSTITUTE(A2,”https://”,””),”www.”,””) |
In this example, we have URLs in column A that start with “https://” and include “www.”. We want to format the URLs by removing these components. The formula =SUBSTITUTE(SUBSTITUTE(A2,"https://",""),"www.","") cell B2 uses nested SUBSTITUTE functions to remove “https://” and “www.” from the URL. The result is a formatted URL without these components.
5️⃣ Example 5: Replacing Characters in Text
| A | B | |
|---|---|---|
| 1 | Text | Modified Text |
| 2 | ABCDEFG | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,”A”,”X”),”B”,”Y”),”C”,”Z”) |
In this example, we have a text string in column A, and we want to replace specific characters with others. The formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"A","X"),"B","Y"),"C","Z") cell B2 uses nested SUBSTITUTE functions to replace “A” with “X”, “B” with “Y”, and “C” with “Z” in the text. The result is the modified text string.
6️⃣ Example 6: Normalizing File Extensions
| A | B | |
|---|---|---|
| 1 | File Name | Normalized File Extension |
| 2 | report.docx | =SUBSTITUTE(A2,”.”,”_”) |
In this example, we have file names in column A with a period separating the file name and extension. We want to normalize the file extensions by replacing the period with an underscore. The formula =SUBSTITUTE(A2,".","_") in cell B2 uses the SUBSTITUTE function to return the period with an underscore. The result is the normalized file extension.
7️⃣ Example 7: Reformatting Dates
| A | B | |
|---|---|---|
| 1 | Date | Reformatted Date |
| 2 | 2023-12-31 | =SUBSTITUTE(A2,”-“,”/”) |
In this example, we have dates in column A in the format “YYYY-MM-DD.” We want to reformat the dates by replacing the hyphens with forward slashes. The formula =SUBSTITUTE(A2,"-","/") in cell B2 uses the SUBSTITUTE function to return the hyphens with forward slashes. The result is the reformatted date.
8️⃣ Example 8: Removing Non-Numeric Characters
| A | B | |
|---|---|---|
| 1 | Value | Numeric Only |
| 2 | $1,234.56 | =SUBSTITUTE(SUBSTITUTE(A2,”$”,””),”,”,””) |
In this example, we have values in column A that include a currency symbol and commas. We want to extract only the numeric portion of the values. The formula =SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","") in cell B2 uses nested SUBSTITUTE functions to remove the currency symbol and commas. The result is the numeric value “1234.56.”
9️⃣ Example 9: Replacing Text in Formulas
| A | B | |
|---|---|---|
| 1 | Formula | Modified Formula |
| 2 | =SUM(A1:A10) | =SUBSTITUTE(A2,”SUM”,”AVERAGE”) |
In this example, we have formulas in column A, and we want to modify them by replacing a specific function with another function. The formula =SUBSTITUTE(A2,"SUM","AVERAGE") cell B2 uses the SUBSTITUTE function to replace “SUM” with “AVERAGE” in the formula. The result is the modified formula.
🔟 Example 10: Replacing Text in Text Functions
| A | B | |
|---|---|---|
| 1 | Text Function | Modified Text Function |
| 2 | =LEFT(A1, 5) | =SUBSTITUTE(A2,”LEFT”,”RIGHT”) |
In this example, we have text functions in column A, and we want to modify them by replacing a specific function with another function. The formula =SUBSTITUTE(A2,"LEFT","RIGHT") in cell B2 uses the SUBSTITUTE function to replace “LEFT” with “RIGHT” in the text function. The result is the modified text function.
✨ Part 3: Tips and Tricks
- The SUBSTITUTE function is a powerful tool for manipulating text in Excel. You can perform complex text transformations and substitutions by nesting them with other functions.
- You can chain various SUBSTITUTE functions to target each replacement separately when replacing multiple characters or substrings.
- The SUBSTITUTE function is case-sensitive, so keep in mind the case of the text you are substituting. If you need case-insensitive substitutions, consider using additional functions like LOWER or UPPER in combination with SUBSTITUTE.
You can efficiently manipulate and transform text to suit your specific business needs within Excel by leveraging the SUBSTITUTE function and other functions.