For replace a string text, numbers, or symbols, Microsoft Excel offers a function called SUBSTITUTE. This function replaces the specified string with your choice of string. We’ll show you how to use it in your spreadsheets.
What to know when using the SUBSTITUTE function
When you use the SUBSTITUTEfunction, know that it is case sensitive, so you will have to use it accordingly. Also, you cannot specify wildcard entries in the function. You can use the function with your hardcoded values, just like with cell references .
The function even allows you to choose which instances of your specified string to change. That way, if you only want to change the first occurrence of a string, you can.
Replace occurrences of a string using SUBSTITUTE in Excel
To start using the function, open your spreadsheet with Microsoft Excel.
In the spreadsheet, select the cell in which you want to display the result. In the following example, we will replace HTGwith How-To Geek.
In your selected cell, type the following function and press Enter.
In this function:
- B2 : Is the cell with the content you want to replace.
- HTG : This is the original string that you want to find and replace with the new string.
- How To Geek : This is the new string that will replace the old string.
= SUSTITUIR (B2, "HTG", "Geek de instrucciones")
You will see that the function has replaced the values defined in the arguments.
Another scenario where you may want to use this feature is when you want to change the country code for phone numbers. For example, if you have a list of phone numbers that contain +91the country code, you can use the SUBSTITUTEfunction to make all these phone numbers used +1as country code.
To do this, use the SUBSTITUTEfunction with the following arguments:
=SUSTITUIR(B2,"91","1",1)
As you can see, in the above function, we have specified 1at the end. This tells the function to only change the first occurrence of 91a 1. If the remaining numbers in a phone number contain 91the function will not change that. This helps you avoid ending up with the wrong phone numbers.
And this is how you use the excel function SUBSTITUTEto change multiple strings in your spreadsheets.
Another way to change the content of your spreadsheet is use Excel’s find and replace function . Check out our guide on that if you’re interested.