Spreadsheet formulas explained, side by side

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

AppSupportSyntax & notes
Excel✔ Built inSUBSTITUTE(text, old_text, new_text, [instance_num])
Google Sheets✔ Built inSUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
Airtable✔ Built inSUBSTITUTE(string, old_text, new_text, [index])
Notion✔ Built inreplaceAll(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.
Try SUBSTITUTE with your own values
Leave empty to replace every occurrence
Result (as Excel & Google Sheets return it)
2024/11/19 10 characters
Your formula, ready to paste
Excel=SUBSTITUTE("2024-11-19", "-", "/") open excel.new ↗
Google Sheets=SUBSTITUTE("2024-11-19", "-", "/") open sheets.new ↗
AirtableSUBSTITUTE("2024-11-19", "-", "/")
NotionreplaceAll("2024-11-19", "-", "/")

Arguments

ArgumentWhat it means
textThe text to change.
old_textThe text to find. Case-sensitive.
new_textThe replacement. Use "" to delete old_text.
[instance_num] optionalWhich occurrence to replace (1 = first). Leave out to replace all of them.

Examples

FormulaResultWhyCopy
=SUBSTITUTE("2024-11-19", "-", "/")2024/11/19Every occurrence is replaced.
=SUBSTITUTE("2024-11-19", "-", "/", 2)2024-11/19Only the second dash.
=SUBSTITUTE("(555) 123-4567", " ", "")(555)123-4567Use "" to delete a character.
=SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "")No bracketsNest 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”).

Part of the Text functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.