What SUBSTITUTE does
SUBSTITUTE replaces text by matching the content you name, not by counting character positions. That makes it a good choice for cleanup work when the target word, code, or separator may appear more than once.
Practical examples
Replace all dashes with spaces
Suppose A2 contains North-West-Region. Enter this formula in B2:
=SUBSTITUTE(A2,"-"," ")
The result is North West Region. With instance_num omitted, both dashes become spaces. This is useful when imported names use separators you want to clean up.
Replace only the second occurrence
Suppose A2 contains North,South,East,West. Enter this formula in B2:
=SUBSTITUTE(A2,","," | ",2)
The result is North,South | East,West. The final argument, 2, selects the second comma; the first and third commas stay in place. The spaces around the vertical bar come from the replacement text, " | ".
Common mistakes and notes
SUBSTITUTE matches text, not positions
If you know the exact starting character, REPLACE is usually a better fit. SUBSTITUTE is better when you know the text to find.
Matching is case-sensitive
=SUBSTITUTE("Red","r","x") returns Red: the lowercase r does not match the uppercase R. Watch the letter case when a replacement seems to fail.
Omitting instance_num replaces every match
That default is convenient, but it can change more of the string than you intended if a value repeats.