SUBSTITUTE Function Generator
SUBSTITUTE replaces occurrences of a specific text inside a string. Lets you target the Nth occurrence only. Use REPLACE when you know exact position.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Try it — generate a SUBSTITUTE formula
Examples
Remove dashes from SSN
=SUBSTITUTE(A2, "-", "")Replaces all dashes with empty string.
Replace only first occurrence
=SUBSTITUTE(A2, " ", "_", 1)Replaces only the first space.
Normalize phone numbers
=SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "")Nested SUBSTITUTE strips parens.
Tips
- ✓Omit instance_num to replace all occurrences.
- ✓Case-sensitive.
- ✓Chain multiple SUBSTITUTEs to clean formatting: SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", "").
Related Formulas
TRIM
TRIM removes leading and trailing spaces and collapses multiple internal spaces ...
FIND
FIND returns the starting position of one text string inside another. Case-sensi...
Text Split / Extract
Split text into parts, extract substrings, or parse structured data like email a...
Need a different formula?
Describe any formula in plain English and FormulaPad will generate it.
Try FormulaPad Free