Excel function · Changing text

SUBSTITUTE

Replaces a piece of text with another wherever it appears.

What it does

SUBSTITUTE finds every occurrence of a text and replaces it with another. You can limit it to one occurrence, such as only the second hyphen.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

text: The text to change, usually a cell.
old_text: The text to replace. It is case-sensitive.
new_text: What to put in its place. Use "" to delete it.
instance_num: Which occurrence to replace. Leave it out to replace all of them.

A phone number uses hyphens. How do you show it with spaces instead?

	A	B
1	Phone	With spaces
2	030-555-0182	030 555 0182

The formula in B2:

=SUBSTITUTE(A2,"-"," ")

Result: 030 555 0182

Both hyphens in 030-555-0182 are replaced by a space, giving 030 555 0182. Leaving out the fourth argument replaces every occurrence.

Mixing up case. "ABC" doesn't match "abc", so nothing is replaced.
Expecting a number back after removing characters. The result is still text; wrap it in VALUE if you need a number.

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.