SUBSTITUTE Function in Excel
Intermediate
Replaces existing text with new text in a text string.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Example
Use Case
Replace 'Quarter 1' with 'Q1' in all cells.
Common Errors to Avoid
- Case-sensitive replacement - 'Text' won't match 'text'
- Without instance_num, replaces ALL occurrences
- Different from REPLACE - SUBSTITUTE finds text, REPLACE uses position
Pro Tips
Use instance_num to replace specific occurrence: =SUBSTITUTE(A1,'/','-',2) replaces only 2nd slash. Chain multiple SUBSTITUTEs for multiple replacements. Case-sensitive unlike Find & Replace dialog. Use TRIM afterwards if replacing with spaces.
Need help with this formula?
Our AI Formula Generator can write this for you instantly.
Try AI Formula Generator →