SUBSTITUTE Function in Excel, Google Sheets, Airtable & Notion
SUBSTITUTE swaps every occurrence of one piece of text for another, or just the nth one. SUBSTITUTE("2024-11-19", "-", "/") is “2024/11/19”.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | SUBSTITUTE(text, old_text, new_text, [instance_num]) |
| Google Sheets | ✔ Built in | SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) |
| Airtable | ✔ Built in | SUBSTITUTE(string, old_text, new_text, [index]) |
| Notion | ✔ Built in | replaceAll(text, pattern, replacement)The pattern is a regular expression, so characters like . ( ) $ + must be escaped with a backslash (the copy button does this). replace() changes only the first match. For the nth match the tester builds a split/join formula. |
| Excel | =SUBSTITUTE("2024-11-19", "-", "/") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =SUBSTITUTE("2024-11-19", "-", "/") | open sheets.new ↗ |
| Airtable | SUBSTITUTE("2024-11-19", "-", "/") | |
| Notion | replaceAll("2024-11-19", "-", "/") |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Arguments
| Argument | What it means |
|---|---|
text | The text to change. |
old_text | The text to find. Case-sensitive. |
new_text | The replacement. Use "" to delete old_text. |
[instance_num] optional | Which occurrence to replace (1 = first). Leave out to replace all of them. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=SUBSTITUTE("2024-11-19", "-", "/") | 2024/11/19 | Every occurrence is replaced. | |
=SUBSTITUTE("2024-11-19", "-", "/", 2) | 2024-11/19 | Only the second dash. | |
=SUBSTITUTE("(555) 123-4567", " ", "") | (555)123-4567 | Use "" to delete a character. | |
=SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "") | No brackets | Nest SUBSTITUTE to remove several characters. |
Examples use Excel / Google Sheets syntax unless noted.
How SUBSTITUTE works
SUBSTITUTE replaces by content: you say what text to look for, and every match is changed. That makes it the formula version of Find & Replace, useful for removing currency symbols, swapping separators, stripping brackets from phone numbers or replacing line breaks (CHAR(10)) with spaces. Excel's separate REPLACE() function replaces by position (characters 5 to 8) instead.
SUBSTITUTE is case-sensitive: replacing “apple” leaves “Apple” alone. To replace several different characters, nest calls: =SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", ""). The optional fourth argument replaces only that occurrence, which, combined with LEN, is the classic trick for finding the last delimiter in a string.
Airtable's SUBSTITUTE has the same arguments, including the optional index. Notion uses replaceAll(text, pattern, replacement), where the pattern is a regular expression. That makes it more powerful (replaceAll(prop("Phone"), "[^0-9]", "") keeps only digits), but characters such as . and ( need a backslash to be matched literally.
Frequently Asked Questions
- How do I remove a character from text?
- Replace it with empty text:
=SUBSTITUTE(A2, "-", ""). - What is the difference between SUBSTITUTE and REPLACE?
- SUBSTITUTE finds text by content (“replace every dash”). REPLACE works by position (“replace 3 characters starting at character 5”).