Excel SUBSTITUTE Function

The SUBSTITUTE function is one of the (TEXT) functions of Excel. It replaces existing text with

new text in a text string.

We can find this function in TEXT of insert function Tab.

How to use SUBSTITUTE function in excel

1. Click on empty cell (like F5 )

2. Click on fx on the below of font word (or press shift+F3)

3. In insert function tab you will see all functions

4. Select TEXT category

5. Select SUBSTITUTE function

6. Then select ok

7. In function arguments Tab you will see SUBSTITUTE function

8. Text is the text or the reference to a cell containing text in which you want to substitute

characters

9. Old_text is the existing text you want to replace. If the case of Old_text does not match the

case of text, SUBSTITUTE will not replace the text

10. New text is the text you want to replace Old text with

11. Instance num specifies which occurrence of Old_text you want to replace. If omitted, every

instance of Old_text is replaced

12. You will see the results in formula result section

Examples of SUBSTITUTE function in Excel

1. Replace “apple” with “orange” in cell A1: =SUBSTITUTE(A1,”apple”,”orange”)
2. Replace all instances of “m” with “n” in cell A2: =SUBSTITUTE(A2,”m”,”n”, -1)
3. Replace the second instance of “dog” with “cat” in cell A3: =SUBSTITUTE(A3,”dog”,”cat”,2)
4. Replace any “@” symbols in cell A4 with a space: =SUBSTITUTE(A4,”@”,” “)
5. Replace any “-” symbols in cell A5 with a forward slash: =SUBSTITUTE(A5,”-“,”/”)
6. Remove all spaces in cell A6: =SUBSTITUTE(A6,” “,””)
7. Remove any leading zeros in a cell A7: =SUBSTITUTE(A7,”0″,””,1)
8. Replace any non-numeric characters in a cell A8 with a blank space: =SUBSTITUTE(A8,”[^0-9]”,””)
9. Replace any instances of “north,” “south,” “east,” or “west” in cell A9 with “N,” “S,” “E,” or “W”: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A9,”north”,”N”),”south”,”S”),”east”,”E”),”west”,”W”)
10. Replace a specific character at a certain position in a cell A10: =SUBSTITUTE(A10,MID(A10,3,1),”Z”,3)

How do you use the SUBSTITUTE function to replace text in a cell?

The SUBSTITUTE function in Excel is a powerful tool that allows you to replace one or more instances of a particular character or text string within a given cell. Here’s how to use the SUBSTITUTE function to replace text in an Excel cell:

Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

• ‘text’ is the cell reference or text string containing the original text to be replaced.
• ‘old_text’ is the specific text string or character that you want to replace.
• ‘new_text’ is the replacement text you want to use instead of the old_text.
• [instance_num] (optional) is the number specifying which occurrence of old_text you want to replace. If you omit this argument, Excel will replace all occurrences of old_text.

Example 1: Suppose you have a list of names in column A, and you want to replace all instances of “John” with “Jack”. You can use the following formula in cell B1:

=SUBSTITUTE(A1, “John”, “Jack”)

This formula will replace all instances of “John” with “Jack” in cell A1.

Example 2: Now suppose you only want to replace the second instance of “John” with “Jack”. You can use the following formula in cell B2:

=SUBSTITUTE(A2, “John”, “Jack”, 2)

This formula will replace only the second instance of “John” with “Jack” in cell A2.

Example 3: Suppose you have a long text string in cell A3 that contains multiple special characters like ‘@’, ‘#’, and ‘$’. You can use the following formula in cell B3 to remove all the special characters:

=SUBSTITUTE(A3,”@”,””) & SUBSTITUTE(A3,”#”,””) & SUBSTITUTE(A3,”$”,””)

This formula will remove all ‘@’, ‘#’, and ‘$’ characters from cell A3.

Example 4: You can also use the SUBSTITUTE function to replace multiple strings at once. For example, if you have a list of URLs in cell A4 that all end with “.com”, you can use the following formula in cell B4 to replace “.com” with “.net”:

=SUBSTITUTE(SUBSTITUTE(A4,”.com”,”.net”),”.COM”,”.NET”)

This formula will replace both “.com” and “.COM” with “.net” and “.NET” respectively.

In conclusion, the SUBSTITUTE function in Excel is a valuable tool that allows you to quickly and easily replace text within a cell, making it an indispensable part of your Excel toolkit.

Can you use the SUBSTITUTE function to replace multiple instances of a word in a cell?

Yes, you can use the SUBSTITUTE function to replace multiple instances of a word in an Excel cell.

Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

• ‘text’ is the cell reference or text string containing the original text to be replaced.
• ‘old_text’ is the specific text string or character that you want to replace.
• ‘new_text’ is the replacement text you want to use instead of the old_text.
• [instance_num] (optional) is the number specifying which occurrence of old_text you want to replace. If you omit this argument, Excel will replace all occurrences of old_text.

To replace multiple instances of a word in a cell, you need to leave the instance_num argument blank, as shown in the examples below:

Example 1: Suppose you have a cell A1 that contains the following text: “The quick brown fox jumps over the lazy dog. The quick brown fox is very fast!” and you want to replace all instances of the word “quick” with “speedy”. You can use the following formula in cell B1:

=SUBSTITUTE(A1,”quick”,”speedy”)

This formula will replace all instances of “quick” with “speedy” in cell A1.

Example 2: Now suppose you have a cell A2 that contains the following text: “John, John, and John are coming to the party.” and you want to replace all instances of “John” with “Jack”. You can use the following formula in cell B2:

=SUBSTITUTE(A2,”John”,”Jack”)

This formula will replace all instances of “John” with “Jack” in cell A2.

Example 3: You can also use the SUBSTITUTE function to remove unwanted characters from a cell. For example, suppose you have a cell A3 that contains the following text: “abc-123-def-456-ghi” and you want to remove all hyphens “-“. You can use the following formula in cell B3:

=SUBSTITUTE(A3,”-“,””)

This formula will remove all hyphens “-” from cell A3.

In conclusion, the SUBSTITUTE function is a versatile tool that allows you to replace multiple instances of a word or character in an Excel cell. Simply leave the instance_num argument blank to replace all occurrences of the old_text in the cell with the new_text.

What is the syntax for the SUBSTITUTE function?

The syntax for the SUBSTITUTE function in Excel is as follows:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Where:

• ‘text’ is the cell reference or text string containing the original text to be replaced.
• ‘old_text’ is the specific text string or character that you want to replace.
• ‘new_text’ is the replacement text you want to use instead of the old_text.
• [instance_num] (optional) is the number specifying which occurrence of old_text you want to replace. If you omit this argument, Excel will replace all occurrences of old_text.

Let’s break down each element of the syntax:

1. text: This is the cell reference or text string that you want to modify using the SUBSTITUTE function.
2. old_text: This is the text string or character that you want to replace within the text. It can be a maximum of 255 characters long.
3. new_text: This is the replacement text that you want to substitute for the old_text.
4. instance_num (optional): This parameter specifies which occurrence of the old_text you want to replace with the new_text. If you leave this parameter blank, SUBSTITUTE will replace all instances of old_text in the text.

Here are some examples to illustrate the syntax of the SUBSTITUTE function:

Example 1: Suppose you have a cell A1 that contains the following text: “happy birthday” and you want to replace “birthday” with “anniversary”. You can use the following formula:

=SUBSTITUTE(A1,”birthday”,”anniversary”)

Example 2: Now suppose you only want to replace the second occurrence of the word “birthday”. You can use the following formula:

=SUBSTITUTE(A2,”birthday”,”anniversary”,2)

Example 3: You can also nest the SUBSTITUTE function inside other functions to perform more complex operations. For example, suppose you want to remove all spaces from a text string in cell A3. You can use the following formula:

=SUBSTITUTE(A3,” “,””)

In this example, SUBSTITUTE is nested inside another function that removes spaces.

In conclusion, understanding the syntax of the SUBSTITUTE function is essential to using it effectively in Excel. By specifying the text you want to modify, the old_text you want to replace, and the new_text you want to substitute, you can easily manipulate text strings in your spreadsheets.

Is it possible to use wildcards with the SUBSTITUTE function?

No, it is not possible to use wildcards with the SUBSTITUTE function in Excel. The SUBSTITUTE function only allows you to search for and replace a specific text string or character within a given cell.

However, you can use other functions in combination with SUBSTITUTE to achieve wildcard-like behavior. Here are two examples:

Example 1: Using the FIND and SUBSTITUTE functions together

Suppose you have a cell A1 that contains the text “The quick brown fox jumps over the lazy dog.” and you want to replace all words that start with the letter “f” with the word “fast”. You can use the following formula:

=SUBSTITUTE(A1,MID(A1,FIND(” f”,A1),IFERROR(FIND(” “,A1,FIND(” f”,A1)),LEN(A1))-FIND(” f”,A1)),” fast”)

Explanation:

• The FIND function searches for the position of the space before the word that starts with “f”.
• The MID function extracts the word that starts with “f” by starting at the position found by the FIND function and ending at the position of the next space.
• The SUBSTITUTE function replaces the extracted word with “fast”.

Example 2: Using the SUBSTITUTE and CONCATENATE functions together

Suppose you have a cell A2 that contains a list of names separated by commas, and you want to remove all names that start with the letter “A”. You can use the following formula:

=SUBSTITUTE(CONCATENATE(IFERROR(IF(LEFT(TRIM(MID(SUBSTITUTE(A2,”,”,REPT(” “,255)),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A2,”,”,””)))),255))=”A”,””,TRIM(MID(SUBSTITUTE(A2,”,”,REPT(” “,255)),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A2,”,”,””)))),255))),””),”,”,”,”)

Explanation:

• The SUBSTITUTE function replaces all commas with spaces.
• The CONCATENATE function concatenates all the names into a single string.
• The IF, LEFT, TRIM, and MID functions are used to extract each name from the concatenated string and check if it starts with the letter “A”. If it does not start with “A”, the name is included in the final output. If it does start with “A”, the name is excluded.
• The final SUBSTITUTE function replaces all spaces with commas to produce the final output.

In conclusion, while you cannot use wildcards with the SUBSTITUTE function directly, you can use other Excel functions in combination with SUBSTITUTE to achieve similar results.

Can the SUBSTITUTE function be used to replace text in multiple cells at once?

No, the SUBSTITUTE function in Excel cannot be used to replace text in multiple cells at once. The SUBSTITUTE function is designed to operate on a single cell at a time.

However, there are several ways to replace text in multiple cells at once. One way is to use Find and Replace:

1. Select the range of cells that you want to modify.
2. Press Ctrl+H to open the Find and Replace dialog box.
3. In the “Find what” field, enter the text string or character that you want to replace.
4. In the “Replace with” field, enter the replacement text.
5. Click “Replace All” to make the changes across all selected cells.

Another way is to use a combination of functions such as CONCATENATE, SUBSTITUTE, and TEXTJOIN to modify the contents of multiple cells at once. Here’s an example:

Suppose you have a range of cells (A1:A10) that contain various names and you want to replace all instances of the letter “a” with the letter “e”. You can use the following formula:

=TEXTJOIN(“,”,TRUE,SUBSTITUTE(A1:A10,”a”,”e”))

Explanation:

• The SUBSTITUTE function replaces all instances of “a” with “e” in each cell within the range A1:A10.
• The TEXTJOIN function combines the modified cell values into a single text string, separated by commas.
• The TRUE argument in the TEXTJOIN function means that it will ignore any blank cells in the range.

Once you have the modified text as a single string, you can paste it back into the original range of cells using Paste Special > Values.

In conclusion, while the SUBSTITUTE function cannot be used to replace text in multiple cells at once, there are other Excel functions and techniques that you can use to achieve similar results.

How do you nest the SUBSTITUTE function inside other functions in Excel?

Nesting the SUBSTITUTE function inside other functions in Excel can be a powerful way to manipulate text data. Here’s how to do it:

1. Identify the formula that you want to use with SUBSTITUTE and determine where to insert the SUBSTITUTE function.
2. Open parentheses after the formula name, and then enter the cell reference or text string (i.e., ‘text’) as the first argument.
3. After entering the first argument, add a comma to separate it from the second argument, which is the ‘old_text’ string or character you want to replace.
4. Inside quotes, enter the ‘old_text’ string or character you want to replace.
5. Add another comma to separate the ‘old_text’ string from the third argument, which is the ‘new_text’ string or character that you want to use instead of the ‘old_text’.
6. Inside quotes, enter the ‘new_text’ string or character.
7. Close the parentheses for the SUBSTITUTE function.
8. Finish typing the remaining arguments for the formula.

Here are some examples of how to nest the SUBSTITUTE function inside other functions:

Example 1: Using the CONCATENATE function

Suppose you have a list of names in cells A1:A10 and you want to concatenate them into a single cell, separated by commas, but you want to replace any spaces with underscores. You can use the following formula:

=SUBSTITUTE(CONCATENATE(A1:A10), ” “, “_”)

Explanation:

• The CONCATENATE function combines all the cells in A1:A10 into a single text string.
• The SUBSTITUTE function replaces any spaces in the concatenated string with underscores.

Example 2: Using the LEFT function

Suppose you have a list of email addresses in cells A1:A10 and you want to extract only the username (the part before the “@” symbol) from each address. You can use the following formula:

=SUBSTITUTE(LEFT(A1:A10,FIND(“@”,A1:A10)-1),”.”,””)

Explanation:

• The LEFT function extracts the first characters of each email address up to the “@” symbol.
• The SUBSTITUTE function removes any periods (“.”) from the extracted username.

Example 3: Using the IFERROR and LEN functions

Suppose you have a list of phone numbers in cells A1:A10, some of which are missing an area code. You want to insert the area code “555” before each phone number that does not have one. You can use the following formula:

=IFERROR(SUBSTITUTE(A1:A10,”(“,”(555) “),SUBSTITUTE(A1:A10,LEN(A1:A10)-7,”(555) “&RIGHT(A1:A10,7)))

Explanation:

• The IFERROR function checks if the phone number already contains parentheses around the area code. If it does, it adds “555” before the existing parentheses. If it doesn’t, the SUBSTITUTE function adds parentheses with “555” before the last seven digits of the phone number using the LEN and RIGHT functions.

In conclusion, nesting the SUBSTITUTE function inside other functions allows you to perform complex text manipulations in Excel. By understanding the syntax and how to combine functions, you can customize formulas to fit your specific needs.

Does CASE-SENSITIVITY matter when using the SUBSTITUTE function?

Yes, case-sensitivity matters when using the SUBSTITUTE function in Excel. The SUBSTITUTE function is case-sensitive, which means that it will only replace text strings or characters that match the case of the old_text argument.

For example, suppose you have a cell A1 that contains the sentence “The quick brown fox jumps over the lazy dog.” and you want to replace all instances of the word “fox” with the word “cat”. If you use the following formula:

=SUBSTITUTE(A1,”fox”,”cat”)

The result will be: “The quick brown cat jumps over the lazy dog.”

However, if you try to replace “Fox” instead of “fox” using the same formula, no changes will be made because the SUBSTITUTE function is case-sensitive:

=SUBSTITUTE(A1,”Fox”,”cat”)

The result will still be: “The quick brown fox jumps over the lazy dog.”

To make the SUBSTITUTE function non-case-sensitive, you can use other functions such as LOWER or UPPER to convert all text to either lowercase or uppercase before performing the substitution. Here’s an example:

Suppose you have a cell A2 that contains the text “The quick BROWN Fox jumps over the lazy DOG.” and you want to replace all instances of the word “brown” regardless of its case with the word “white”. You can use the following formula:

=SUBSTITUTE(LOWER(A2),LOWER(“brown”),”white”)

Explanation:

• The LOWER function converts all text in cell A2 to lowercase.
• The SUBSTITUTE function replaces all instances of the word “brown” (in lowercase) with the word “white”.

The result will be: “the quick white fox jumps over the lazy dog.”

In conclusion, the SUBSTITUTE function in Excel is case-sensitive, so you must ensure that the old_text argument matches the case of the text string or character you want to replace. If necessary, you can use other functions such as LOWER or UPPER to make the SUBSTITUTE function non-case-sensitive.

Can the SUBSTITUTE function be used to replace special characters or symbols in a cell?

Yes, the SUBSTITUTE function in Excel can be used to replace special characters or symbols in a cell. The SUBSTITUTE function allows you to replace any text string or character in a given cell with another text string or character.

To replace a special character or symbol in a cell, you need to identify the specific character you want to replace and use it as the old_text argument in the SUBSTITUTE function. Here are some examples:

Example 1: Replacing the pound symbol (£) with the dollar symbol ($)

Suppose you have a cell A1 that contains the text “Total cost: £50” and you want to replace the pound symbol with the dollar symbol. You can use the following formula:

=SUBSTITUTE(A1,”£”,”$”)

Explanation:

• The SUBSTITUTE function replaces all instances of the pound symbol (“£”) with the dollar symbol (“$”).

The result will be: “Total cost: $50”.

Example 2: Replacing line breaks with spaces

Suppose you have a cell A2 that contains a multi-line address, with each line separated by a line break character (ASCII character 10). You want to replace the line breaks with spaces to create a single-line address. You can use the following formula:

=SUBSTITUTE(A2,CHAR(10),” “)

Explanation:

• The SUBSTITUTE function replaces all instances of the line break character (CHAR(10)) with a space.

The result will be a single-line address without line breaks.

Example 3: Replacing non-alphanumeric characters with underscores

Suppose you have a cell A3 that contains a product code that includes various non-alphanumeric characters such as spaces, dashes, and parentheses. You want to replace these characters with underscores. You can use the following formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,” “,”“),”-“,”“),”(“,”“),”)”,”“)

Explanation:

• The SUBSTITUTE function replaces spaces with underscores.
• The nested SUBSTITUTE functions replace dashes and parentheses with underscores.

The result will be a product code with all non-alphanumeric characters replaced by underscores.

In conclusion, the SUBSTITUTE function in Excel can be used to replace special characters or symbols in a cell. By identifying the specific character you want to replace and using it as the old_text argument in the SUBSTITUTE function, you can easily manipulate text data in your spreadsheet.

Are there any limitations to the SUBSTITUTE function, such as the number of characters it can replace?

The SUBSTITUTE function in Excel is used to replace a specific instance or multiple instances of a text string within another text string. The syntax for the function is as follows:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Here, “text” is the original text string where we want to replace some characters, “old_text” is the text that we want to replace, “new_text” is the new text to replace the old text with, and “instance_num” (optional) specifies which occurrence of old_text we want to replace.

For example, let’s say we have a cell containing the text string “apple, orange, banana, grapefruit”. To replace the comma (“,”) with a semicolon (“;”), we can use the following formula:

=SUBSTITUTE(A1,”,”,”;”)

This will give us the result: “apple; orange; banana; grapefruit”.

However, there are some limitations to the SUBSTITUTE function that you should be aware of. One limitation is that it can only replace one character or text string at a time. In other words, if we want to replace two different characters in the same text string, we need to use two separate SUBSTITUTE functions.

Another limitation is that SUBSTITUTE can only replace up to 8,192 characters in a single cell. If the text string is longer than this, the function will return an error.

Finally, the SUBSTITUTE function is case-sensitive. This means that if we want to replace both uppercase and lowercase instances of a character, we need to use two separate SUBSTITUTE functions.

In conclusion, while the SUBSTITUTE function in Excel is a useful tool for replacing specific instances of a text string within another text string, it does have its limitations. It can only replace one character or text string at a time, has a maximum limit of 8,192 characters in a cell, and is case-sensitive.

Related posts:

Excel PROPER Function Excel LEFT Function Excel TEXTJOIN Function Keyboard Shortcuts for Applying Borders in Excel