Difference between REPLACE and SUBSTITUTE in Microsoft Excel
REPLACE and SUBSTITUTE are two common functions with quite similar names in Excel. However, the way to use REPLACE & SUBSTITUTE is different.
Microsoft Excel is a flexible tool that prides itself on providing users with a wide range of useful data analysis functions. But if you're still learning how to work with spreadsheets, and finding the right function for the job at hand can be challenging, some function names can also confuse you about how to actually use them.
If you're trying to change the text in a cell, you might be confusing the REPLACE and SUBSTITUTE functions. This article will help you solve that problem.
Use the REPLACE function in Excel
Suppose there is a list of manufacturer information for products on sale. Some of the information in the table is old, so a bit of data need to be changed to update it.
The REPLACE function changes certain characters in the contents of certain cells with a different set of text. The replacement text can be of any length and it will be set exactly to the number of characters after the start of the original text string.
Assuming there's a new way to report production code, this spreadsheet needs to be updated to incorporate that.
1. Select cell D7 , the cell below says New code .
2. In the cell itself or the formula bar, enter or paste the formula below:
=REPLACE(B2,2,3,"PTM")
3. Press Enter .
Inside this cell, the example just took the text from cell B2 ("6418229"), and starting at the 2nd character (number 4), has been replaced 3 characters (number 418) with the character PTM (equivalent to the PTM character). equivalent to Portland Mechanics).
The replacement text need not be the same length as the number of characters to be replaced. If you only want to replace one character, you can change the 3 in the formula above to 1, ending with the 9-character string "6PTM18229" in cell D7.
Using the SUBSTITUE function in Excel
In this example, suppose Portland Mechanics was later acquired and renamed. You know what you're looking for, so you can use the SUBSTITUE function to replace specific text with another new text.
1. Select cell D4, which is under Updated brand .
2. In that cell or the formula bar, type or paste the formula below:
=SUBSTITUTE(A2,"Portland","Anodyne")
3. Press Enter .
Inside this cell, the example just took the text from cell A2 and replaced the word Portland with Anodyne, creating a new name - Anodyne Mechanics in cell D4 .
Suppose you leave a note explaining the name change for the next viewer of the document and want to save time by using the SUBSTITUE function. When you edit it in cell G5 to refer to cell G3 as shown below, you will see all the words Portland replaced with Anodyne.
This is not what the example wants in this case, so it is necessary to add [instance_num] to specify that only one of the locations Portland appears to be replaced.
To do this, select cell G7 and enter this formula in the cell or formula bar:
=SUBSTITUTE(G3,"Portland","Anodyne",2)
Adding the number 2 tells Excel that it only wants to change the second position of the word Portland.
Although the names are similar, the REPLACE and SUBSTITUE functions have different uses. If you know the exact location of your new text, use REPLACE. However, if you know the words you intend to replace or need to replace multiple versions of the same content in a cell or in multiple cells, SUBSTITUTE is a better choice.
You should read it
- Basic Excel functions that anyone must know
- How to use Hlookup function on Excel
- How to use the IFS function in Excel 2016
- How to use the SUM function to calculate totals in Excel
- How to use the LEN function in Excel
- How to fix the SUM function doesn't add up in Excel
- How to use the WRAPROWS function in Excel
- Save time with these text formatting functions in Microsoft Excel
- How to use the MOD function and QUOTIENT function in Excel
- How to use the NPER function in Excel to plan loans and savings
- How to use the FIND and REPLACE functions in Excel?
- How to use MID functions to get strings in Excel
Maybe you are interested
First success in restoring vision using stem cell treatment
Transparent solar cells help smartphones charge themselves with sunlight
Tips for formatting cells in Excel for professional spreadsheets
How to Justify Text in Cells on Excel - Adjust Text Spacing
What is a bifacial solar cell? What are the advantages?
Successfully developed 'indoor solar cells' with conversion efficiency up to 37%