When cleaning up messy data in Google Sheets, you frequently encounter text strings that need modification. Perhaps you need to remove a specific prefix from a list of part numbers, or replace every instance of “USA” with “United States”.
Google Sheets provides two distinct formulas for text manipulation: REPLACE and SUBSTITUTE. Understanding the difference between the two is critical for efficient data cleaning.
In this guide, you will learn the exact syntax for both functions and when to use them.
SUBSTITUTE: Replacing Specific Words or Characters
Use the SUBSTITUTE function when you know exactly what text you want to change, regardless of where it is located within the cell.
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])- text_to_search: The cell containing the messy data.
- search_for: The exact word or character you want to remove or change (must be in quotation marks). Note that this is case-sensitive.
- replace_with: The new text you want to insert. If you simply want to delete the old text, use empty quotes (
"").
SUBSTITUTE Example
Imagine cell A2 contains the text: Sales_Report_2023_Final.pdf. You want to replace the word “Final” with “Draft”.
=SUBSTITUTE(A2, "Final", "Draft")Google Sheets scans the entire string, finds “Final”, and swaps it, resulting in Sales_Report_2023_Draft.pdf.
REPLACE: Replacing Text Based on Position
Use the REPLACE function when you do not care what the actual text says, but you know exactly where it is located in the cell. This is highly useful for standardizing ID formats or masking sensitive data (like credit card numbers).
=REPLACE(text, position, length, new_text)- text: The cell containing the data.
- position: The character number where the replacement should begin.
- length: How many characters you want to delete and replace, starting from that position.
- new_text: The text you want to insert in that space.
REPLACE Example
Imagine cell A2 contains a US phone number: 555-867-5309. You want to hide the first three digits for privacy reasons and replace them with “XXX”.
=REPLACE(A2, 1, 3, "XXX")Google Sheets starts at the 1st character, deletes exactly 3 characters (“555”), and inserts “XXX” in their place. The result is XXX-867-5309.
Combining the Two for Advanced Cleaning
In complex scenarios, you may need to use both. For example, if you want to replace a specific character, but only the second time it appears in a string, you can use the optional [occurrence_number] argument in SUBSTITUTE.
If A2 is product-code-123-A and you want to change only the second hyphen to an underscore:
=SUBSTITUTE(A2, "-", "_", 2)This ignores the first hyphen and outputs product-code_123-A.
By mastering both REPLACE (position-based) and SUBSTITUTE (content-based), you can automate massive data cleanup tasks without ever relying on the manual Find and Replace menu.