Syntax or pattern
Replace specific text inside a cell.
=SUBSTITUTE(text_to_search, search_for, replace_with)5 practical examples
Clean names with SUBSTITUTE
Standardize names imported from forms or CSV files.
=PROPER(TRIM(A2))Adjust the ranges, criteria and sheet names to match your workbook.
Create an ID label
Build a readable code from text and row numbers.
=UPPER(LEFT(A2,3)&"-"&TEXT(ROW(A2),"0000"))Adjust the ranges, criteria and sheet names to match your workbook.
Join address fields
Join address parts while skipping blank cells.
=TEXTJOIN(", ", TRUE, B2:E2)Adjust the ranges, criteria and sheet names to match your workbook.
Remove unwanted characters
Clean copied IDs or imported codes.
=SUBSTITUTE(A2, "#", "")Adjust the ranges, criteria and sheet names to match your workbook.
Split names into parts
Separate text values into columns for later analysis.
=SPLIT(A2, " ")Adjust the ranges, criteria and sheet names to match your workbook.
Common mistakes to avoid
- Not cleaning imported data before splitting or joining it.
- Forgetting that text functions are case-sensitive in some regex patterns.
- Leaving invisible spaces that make lookups fail.
FAQ
What is this Google Sheets page for?
This page gives copy-ready examples for substitute examples so you can understand the pattern and adapt it to real spreadsheet work.
Can I copy these formulas directly?
Yes. Use the copy buttons, then adjust sheet names, ranges, criteria and column references for your own file.
Why does my formula return an error?
The most common causes are mismatched ranges, missing quotes around text criteria, blank source data, or references that do not match your sheet layout.