You may want to do some basic operations you can perform in programs like Word, which allows you to find and replace specific words. However, as you can tell, you must use some functions to make the same operation in your worksheet when working in a spreadsheet.
That’s why you should learn how to use the SUBSTITUTE function in Microsoft Excel!
First and foremost, the SUBSTITUTE function in Excel replaces a particular text or character within a given string. Hence, you can use it o replace all occurrences of a specific text or character or only the first occurrence, depending on how you specify the function.
There are various situations where you can find this function handy. But these are the top 5:
Overall, the SUBSTITUTE function can be a powerful tool for data cleaning, formatting, and editing in Excel. By using it, you can save time and ensure that your data is consistent and accurate.
Because this is an Excel function, the SUBSTITUTE operation works like almost every formula. That’s why you need to enter the syntax following this base:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Of course, you know in which situation you can use this great function. However, let's consider a few examples to understand the syntax and usage of the SUBSTITUTE function.
Suppose you have a string in cell A1 that reads, "I love pizza, and pizza is my favorite food." You want to replace the word "pizza" with "sushi." You can use the following formula in cell B1:
=SUBSTITUTE(A1; “pizza”; “sushi”).
On the other hand, if you have in cell A1 that reads “1,001” but you want to remove the comma from the number, You can use the following formula in cell B1:
=SUBSTITUTE(A1; “,”; “”).
Notice how Excel will replace all occurrences of "," in the cell with an empty string (because the double quote marks indicate that you want to remove everything), effectively removing the comma from the number.
When you have a particular text in a cell but only want to replace the second occurrence of a word, you can use the following formula:
=SUBSTITUTE(A1; “the”; “a”;2).
Note: if you put a capital letter, Excel will take it as another different word.
In conclusion, the SUBSTITUTE function in Excel is a powerful tool that you can use to replace specific text or characters within a given string. You can easily modify lines and rows of data in your Excel spreadsheets by understanding its syntax and usage.