SUBSTITUTE

Swap every occurrence of one piece of text for another, or just one of them.

Syntax

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

Argument	Required	Description
`text`	Required	The text to work on.
`old_text`	Required	What to replace.
`new_text`	Required	What to replace it with. Empty text deletes it.
`instance_num`	Optional	Replace only the nth occurrence. Omitted, every one is replaced.

Examples

Every example below was executed against VisiGrid engine 0.35.0 — not transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.

Formula	Result	Notes
`=SUBSTITUTE("a-b-c","-","+")`	`a+b+c`	Every occurrence, by default.
`=SUBSTITUTE("a-b-c","-","+",2)`	`a-b+c`	Only the second.
`=SUBSTITUTE("1,234",",","")`	`1234`	Empty replacement text deletes. Still text at this point.
`=VALUE(SUBSTITUTE("1,234",",",""))`	`1234`	VALUE turns it into a number you can add up.
`=REPLACE("abcdef",2,3,"X")`	`aXef`	REPLACE swaps by position instead of by content.

Cleaning imported numbers

The most common real use is stripping characters that stop a value being a number — thousands separators, currency symbols, stray units:

=VALUE(SUBSTITUTE(A1, ",", ""))

Without VALUE the result is still text that merely looks numeric, which sorts wrongly and sums to zero. Both steps are needed.

It replaces text, not positions

SUBSTITUTE matches on content. When you know where rather than what, use REPLACE, which swaps a run of characters by position:

=REPLACE("abcdef", 2, 3, "X")     → aXef

Content when you know the text, position when you know the offset.

And it is case-sensitive, like FIND: substituting "st" will not touch "ST".

Excel compatibility

Matches Excel, including the instance argument and deletion via empty text.