All functions / Text

SUBSTITUTE

Replaces specific text with new text.

Syntax

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

Arguments

• text: The text (or a cell containing it).
• old_text: The text to replace.
• new_text: The replacement.
• instance_num (optional): Which occurrence to replace (default: all).

Example

=SUBSTITUTE("2024-01-31", "-", "/") → "2024/01/31"

Practise it

• Cleaning up messy text
• Text surgery with nested formulas
• Repairing numbers and names