How to replace characters in Excel

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

1. Start with the =SUBSTITUTE( function
2. Type in of select the range that contains your text (i.e. B3,)
3. Continue with the character you want to replace "-",
4. Final argument is the new character you want "*"
5. 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.