Excel offers some useful features working with text. One of these is to substitute one character with another. You can use the SUBSTITUTE function to do this. In this tutorial, you will learn how to replace all instances of one character with another in Excel
How to Replace One Character with Another in Excel
The SUBSTITUTE function helps you to replace one character with another. The SUBSTITUTE function substitutes the specified text in a given string. It uses the syntax
Here, the text argument is the string you want to change, old_text is the character to replace, new_text is the one you want to replace with and the instance argument is an optional value that specifies the instance of old_text you want to replace. If the instance is not specified, new_text will replace all instances of old_text. You have to keep in mind that substitute is case-sensitive and does not support wildcards.
The following example contains an employee id dataset. You will replace the character ‘T’ in all the ids with ‘I’ due to a change in policy. The first column has the employee names, the second column has the old ids and the third and last column will have the new ids. To replace the old ids with the new character:
- Go to cell C2. Select it with your mouse.
- Assign the formula to cell C2.
- Press Enter to apply the formula
- Drag the fill handle from C2 to C6 to apply the formula to the whole column.
This will replace all instances of ‘T‘ in the ids with ‘I’.
The SUBSTITUTE function is very helpful when it comes to replacing text. SUBSTITUTE is a built-in function and works in all versions of Excel. It is a simple text function and can be entered as part of other formulas. Though it does not support wildcards, you can use it to replace one or all instance of a character with another.