Excel's built in find and replace feature is fairly useful in most cases. But this can get tedious if you're working with a large workbook or want to set this up dynamically. In this article we're going to show you how to replace characters in Excel using the SUBSTITUTE function.
Syntax
=SUBSTITUTE( text, old character, new character)
Steps
- Start with the =SUBSTITUTE( function
- Type in of select the range that contains your text (i.e. B3,)
- Continue with the character you want to replace "-",
- Final argument is the new character you want "*"
- Type in ) to close function and finish the formula
How
The SUBSTITUTE function does exactly what the name suggests, it replaces a specified text with a new one, a specific number of times. The replacement starts with the first character of the main text. If you do not enter a number of times argument like in this example, the function replaces all such characters. If this argument is provided, the replacement will take place that many times and stop.
=SUBSTITUTE(B2,"-","*")
A brief note about the SUBSTITUTE function is that the function is NOT case-sensitive. If you prefer to replace a text string by position, check out our How to replace a character by position guide.
