Replace or Substitute: Which Excel Text Function Should You Use?

by

REPLACE and SUBSTITUTE are both used to easily replace or substitute specific text or values within a range of cells. In this article, we will explore the differences between the “replace” and “substitute” functions and provide examples of how they are used.

SUBSTITUTE: This function replaces specific text or values within a range of cells by substituting the character or characters you give Excel.

In this example, we want to change the number “90” to “80.” To do this, we will use the SUBSTITUTE function. Here’s a step-by-step guide:

  1. In a cell, type =SUBSTITUTE(
  2. Select the cell you want to make chanes to.
  3. Add a comma, and then input the characters you wish to change. In this example, “90”.
  4. Add a comma, and then input the text you wish to change it into. In this, example, “80”.
  5. Close the parentheses and then press Enter.

Both instances of 90 in the example are replaced with 80. However, you only want to change the second instance of “90,” the one after “English.” To do this, we will add an extra argument in the function. The formula should now be: =SUBSTITUTE(D2,90,80,2). This tells Excel to only substitute the second instance of 90 in the cell.

REPLACE: This function changes a character in a string in a cell based on where the character’s specific position is.

In the same example above, we want to change the second instance of “90” to “80.” Instead of using SUBSTITUTE, we will be using REPLACE. Here’s a step by step guide

  1. In a cell, type =REPLACE(
  2. Select the cell you want to make changes to.
  3. Add a comma, and then input the position of the character you wish to change. In this example, it is on the 23rd position.
  4. Add a comma, and then input the number of characters you wish to change. In this instance, only two characters: “90”
  5. Add a comma, and then input the text you want to replace on the string. In this case, changing 90 to 80.
  6. Close the the parentheses and then press Enter.

Replacing text using Find and Replace. In this example, we want to replace all instances of “US” and “CA” in the column Center Code with “USA” and “CANADA,” respectively. Instead of using the REPLACE or SUBSTITUTE formulas, we will use the Find and Replace feature in Excel. Here’s a step-by-step guide.

  1. Highlight the range of cells you wish to replace.
  2. In the Home tab, click Find & Select, and then Replace.
  3. In the “Find what” Text box, input the text you wish to replace. In this example, “US”
  4. In the “Replace with” Text box, input the text you wish to replace the original with. In this example, “USA”
  5. Click “Replace all” if you want to replace all instances of “US”, otherwise, if you want to replace only one cell, click “Find next” until your specific cell is highlighted and then click “Replace.” Since we want all instance of “US”to be replaced, we will click “Replace All”

Conclusion

Excel’s “replace” and “substitute” functions are powerful tools for data manipulation and analysis. The replace function allows you to replace specific characters within a cell based on their position, while the substitute function allows you to replace specific characters within a cell based on the character itself. The “Find and Replace” feature in Excel is a quick and easy way to replace text in a worksheet. It can be used to replace multiple occurrences at once or to step through occurrences one by one. Mastering these functions and features in Excel can greatly improve your efficiency and effectiveness in working with data.

Related Posts: