Syntax
SUBSTITUTE(text, old_text, new_text, [instance])
| Parameter | Description |
|---|---|
| text | Parameter of the SUBSTITUTE function. |
| old_text | Parameter of the SUBSTITUTE function. |
| new_text | Parameter of the SUBSTITUTE function. |
| [instance] | (Optional.) Parameter of the SUBSTITUTE function. |
Examples
Remove dashes from phone numbers
Formula
=SUBSTITUTE(A2, "-", "")
Returns: "2125551234" — removes all hyphens from "212-555-1234"
Replace old with new text
Formula
=SUBSTITUTE(A2, "Corp.", "Corporation")
Returns: "Acme Corporation" — replaces all instances of "Corp." with "Corporation"
Replace only the 2nd occurrence
Formula
=SUBSTITUTE(A2, "-", "|", 2)
Returns: "A-B|C-D" — replaces only the second hyphen in "A-B-C-D" with a pipe
Common Errors
#VALUE!
The instance_num (4th argument) is less than 1. It must be a positive integer or omitted to replace all.
#N/A
Very rare. SUBSTITUTE returns the original text unchanged if the old_text is not found — it does not error.
Tips
Case sensitive
SUBSTITUTE is case-sensitive. SUBSTITUTE("Hello","hello","Hi") returns "Hello" unchanged. Use a nested UPPER/LOWER if needed.
Remove characters
To remove a character, substitute with empty string: =SUBSTITUTE(A1, " ", "") removes all spaces.
Chain substitutions
Nest SUBSTITUTE calls: =SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","") removes both hyphens and spaces.
Try SUBSTITUTE in Viztab
Import your data and use SUBSTITUTE with 370+ other formulas. No signup required.
Open Viztab