What is the SUBSTITUTE Function
The SUBSTITUTE function replaces occurrences of a specific text inside a string with new text. It is case-sensitive and does not depend on position. You can also specify which occurrence to replace.
Formula: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Practical Example 1
A2 contains 2024-2025. Replace the dash with a slash.
Type: =SUBSTITUTE(A2, -, /)
Result: 2024/2025
Practical Example 2
Replace only the first dash.
Type: =SUBSTITUTE(A2, -, /, 1)
Result: only the first dash is replaced
Practical Example 3
Remove all spaces from a text.
Type: =SUBSTITUTE(A2, space, )
Result: the text with no spaces
Practical Example 4
Remove non-breaking spaces.
Type: =SUBSTITUTE(A2, CHAR(160), space)
Result: the text with normal spaces
When to Use It
SUBSTITUTE is used for reformatting dates, for cleaning delimiters, for removing special characters, for removing non-breaking spaces, and for any formula where specific text needs to be replaced by content.