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
=SUBSTITUTE(A2,"-"," ")
This is useful when imported IDs or names use separators you want to normalize.
Replace only the second occurrence
=SUBSTITUTE(A2,","," | ",2)
This changes only the second comma and leaves earlier commas unchanged.
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") does not change 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.