Lesson 27 · Text
SUBSTITUTE Function
Find and replace text inside cells with SUBSTITUTE in Google Sheets. Clean phone numbers and rename values.
Remove all the dashes from the phone number in A2. Answer in B2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
Replacing text inside a cell
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
SUBSTITUTE swaps every occurrence of one piece of text for another. Replace with "" to delete it:
=SUBSTITUTE(A2, "-", ""): 512-555-0198 → 5125550198=SUBSTITUTE(A2, "Inc.", "LLC"): Northwind Inc. → Northwind LLC
Add the optional 4th argument to replace only a specific occurrence (e.g. just the first dash). SUBSTITUTE is case-sensitive.