SUBSTITUTE Generator
Replace specific text inside a cell with something else, by content rather than position.
SUBSTITUTE
Replace specific text inside a cell with something else, by content rather than position.
The cell containing the text you want to change.
The exact text to look for, in quotes. Matching is case sensitive.
The replacement text, in quotes. Use "" to delete the found text instead.
Optional fourth argument: which occurrence to replace, counting from the left. Delete this argument and the comma before it to replace every occurrence.
=SUBSTITUTE(A2, "St.", "Street", 1)Worked Example
A2 contains the code 2026-Q1-North and you only want the second hyphen turned into a space.
=SUBSTITUTE(A2, "-", " ", 2)Returns: Returns 2026-Q1 North. The first hyphen is untouched because instance_num is 2.
Checks Before You Paste
- •SUBSTITUTE is case sensitive: "st" will not match "St". If case varies in your data, substitute on UPPER(A2) or LOWER(A2), or clean the source values first.
- •Leave instance_num out entirely to replace every occurrence. Supplying 1 changes only the first one, and a value of 0 or a negative number returns #VALUE!.
- •Nest it to strip several characters at once, for example =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", "") to clean up a phone number.