How to Use the SUBSTITUTE Function in Excel

The SUBSTITUTE function in Excel is used to replace one or more instances of a text string with another text string. The syntax of the function is:

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

The arguments of the function are:

• text: The text or cell reference that contains the text to be replaced.
• old_text: The text that you want to replace.
• new_text: The text that you want to use as the replacement.
• instance_num: (Optional) The number of the occurrence that you want to replace. If omitted, all occurrences of old_text will be replaced.

Step by Step

To use the SUBSTITUTE function in Excel, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =SUBSTITUTE( and then select or type the text argument that contains the text to be replaced.
3. Type a comma (,) and then select or type the old_text argument that you want to replace.
4. Type another comma (,) and then select or type the new_text argument that you want to use as the replacement.
5. (Optional) Type another comma (,) and then select or type the instance_num argument that specifies the number of the occurrence that you want to replace. If omitted, all occurrences of old_text will be replaced.
6. Type a closing parenthesis ()) and press Enter to complete the formula.

Example

Let’s say you have a list of product codes that contain dashes (-) and you want to replace them with slashes (/). For example, you want to change A1-B2-C3 to A1/B2/C3. You can use the SUBSTITUTE function to do this. Here is how:

• In cell A1, enter the product code A1-B2-C3.
• In cell B1, enter the formula =SUBSTITUTE(A1,"-","/"). This will replace all dashes (-) in cell A1 with slashes (/).
• Press Enter to see the result in cell B1. It should be A1/B2/C3.

You can also use the SUBSTITUTE function to replace only a specific occurrence of a text string. For example, you want to change only the second dash (-) in A1-B2-C3 to a slash (/). You can use the instance_num argument to do this. Here is how:

• In cell A1, enter the product code A1-B2-C3.
• In cell B1, enter the formula =SUBSTITUTE(A1,"-","/",2). This will replace only the second dash (-) in cell A1 with a slash (/).
• Press Enter to see the result in cell B1. It should be A1-B2/C3.

Here is a table that shows the results of using the SUBSTITUTE function with different arguments:

Other Approaches

There are other ways to substitute characters in Excel besides using the SUBSTITUTE function. Here are some of them:

• Using the REPLACE function: The REPLACE function can also replace a part of a text string with another text string. The difference is that the REPLACE function uses the position and length of the text to be replaced, rather than the text itself. For example, to replace the second dash (-) in A1-B2-C3 with a slash (/), you can use the formula =REPLACE(A1,4,1,"/"). This means replace the text in cell A1, starting from the fourth character, for one character, with a slash (/).
• Using the Find and Replace dialog box: You can also use the Find and Replace dialog box to replace text in Excel. To do this, select the cells that contain the text to be replaced, press Ctrl+H to open the Find and Replace dialog box, enter the text to find and the text to replace, and click Replace All or Replace to perform the replacement.
• Using Flash Fill: Flash Fill is a feature in Excel that can automatically fill in data based on a pattern. To use Flash Fill, enter the first example of the result that you want in a cell next to the original data, and then press Ctrl+E to fill in the rest of the cells. For example, to replace dashes (-) with slashes (/) in product codes, enter A1/B2/C3 in cell B1 next to A1-B2-C3 in cell A1, and then press Ctrl+E to fill in the rest of the cells in column B.