There’s a function in Microsoft Excel that is known as SUBSTITUTE, and its purpose is to replace a string of symbols, numbers, or text with your choice of string. This is an important function for those who use Excel on a daily basis, but how does it work?
How to use SUBSTITUTE function in Microsoft Excel
Here’s the thing: one must keep in mind that the SUBSTITUTE function is case-sensitive; therefore, you must use it accordingly at all times. Furthermore, it is not possible to specify wildcard entries here, though you have the ability to use hard-coded values with this function.
- Open Microsoft Excel
- Select the relevant cell
- Add a new string
- Change the country code for a phone number
1] Open Microsoft Excel
As always, you must first open the program by clicking on the shortcut via the Desktop, Taskbar, or the Start Menu. Once that is done, Microsoft Excel should now be up and ready for work.
2] Select the relevant cell
It is now time to choose the cell in which you want the results to appear. As you can see in the image, we have a cell with the words, TWC discusses & offer Windows 11, Windows 10 Tips, and Tricks. But from the other cell, we are going to make a slight change by replacing the old string with The Windows Club discusses & offers Windows 11, Windows 10 Tips, and Tricks.
3] Add a new string
In order to add a new string with the above description, we use the following information:
=SUBSTITUTE(B2,"TWC","The Windows Club")
Once the new string is added to the relevant cell, hit the Enter key to showcase the changes. As you can see, our old string is located in B2, while the new string can be found in C2. However, you are free to select whichever cell works best.
4] Change the country code for a phone number
If you are tasked with changing out the country code for multiple phone numbers, you can use the SUBSTITUTE function to get this done as well. The task is very much similar to what we’ve discussed above, so let us take a look.
OK, so if your phone number is 81486245014 but you want to change it to 1486245014, we can do this with ease.
All you have to do is add the following formula to the relevant cell:
=SUBSTITUTE(B2,"85","1",1)
Press Enter, and the changes should appear immediately in your Excel document.
Read: Convert JSON to Excel using free online converter tools
What are Substitute and Replace in Excel?
SUBSTITUTE replaces one or more instances of a given text string or character, while REPLACE focuses on changing the characters in a set position of a text string.
What does the TEXT function SUBSTITUTE do?
It replaces one or multiple text strings with other text strings. This function is super useful because users can replace old text in a string with new text.
How do you substitute values in Excel?
Choose the Cell where you want the substituted string to show. For example, that Cell might be B2. From there, use the substitute function to change the string in B2 to a new one.
Is Excel hard to learn?
If you’re a beginner, then we must say Microsoft Excel can be a challenging tool to master. However, when it comes down to learning the basics, this is quite straightforward and will only take a short amount of time. Much easier if you get help from a school or online course.
What are the five most powerful Functions?
There are many functions in Microsoft Excel to choose from but we believe the following are the most important and probably the most used.
- CONCATENATE
- VLOOKUP
- AVERAGE
- SUM
- TEXT
These functions are just a few of many. If you continue to learn about Excel, you will no doubt come across other useful functions.
