Dealing with messy or inconsistent data in Google Sheets? Whether you’re importing data from external sources, copying from websites, or managing survey results, you’ll often run into unwanted spaces, strange symbols, or hidden characters. That’s where TRIM, CLEAN, and SUBSTITUTE functions come in.
These powerful tools help you tidy up your spreadsheets with just a few formulas—no manual editing required. If you’re a beginner, don’t worry—this guide breaks everything down into easy, real-life examples you can start using right away.
Why Cleaning Data Matters
Messy data can cause:
- Incorrect search or filter results
- Broken formulas (like VLOOKUP or MATCH)
- Unexpected blank cells that look filled
- Frustration during sorting or validation
Cleaning your data ensures everything works smoothly, especially when working with large or shared datasets.
Meet the Cleanup Trio: TRIM, CLEAN, SUBSTITUTE
Here’s what each function does:
TRIM(): Removes extra spaces from text (except single spaces between words).CLEAN(): Removes non-printable characters (like line breaks or hidden characters).SUBSTITUTE(): Replaces one text string with another (great for replacing characters or fixing formatting).
Real-Life Example: Cleaning Imported Contact Data
You’ve copied a list of contact names and phone numbers from a PDF or website into Google Sheets, and the data looks like this:
| Name | Phone |
|---|---|
| John Smith | (123)-456-7890 |
| Jane Doe | 123.456.7890 |
| Mark Lee | 1234567890 |
Let’s clean this up!
Step-by-Step: How to Use TRIM, CLEAN, and SUBSTITUTE
1. Use TRIM() to Remove Extra Spaces
=TRIM(A2)
This will remove all leading, trailing, and extra spaces between words in the name “ John Smith ”, leaving:
John Smith
2. Use CLEAN() to Eliminate Hidden Characters
Sometimes copied data includes invisible characters that cause issues. Wrap the TRIM function inside CLEAN:
=CLEAN(TRIM(A2))
This ensures the cell is free of line breaks, tabs, or other non-printing characters.
3. Use SUBSTITUTE() to Standardize Phone Numbers
Phone numbers often come in various formats. You can standardize them by removing dots, dashes, or parentheses:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, ".", ""), "-", ""), "(", "")
This converts (123)-456-7890 or 123.456.7890 into:
1234567890
Want to go further? Remove all non-numeric characters:
=REGEXREPLACE(B2, "[^0-9]", "")
This works great if you want only the digits.
Final Clean Table
| Cleaned Name | Cleaned Phone |
|---|---|
| John Smith | 1234567890 |
| Jane Doe | 1234567890 |
| Mark Lee | 1234567890 |
More Examples and Use Cases
Common Uses for TRIM():
- Removing leading/trailing spaces in names
- Cleaning up pasted CSV data
- Preparing text before VLOOKUP
Common Uses for CLEAN():
- Removing hidden line breaks or symbols from emails, notes, or website data
- Fixing data pasted from Word docs or PDFs
Common Uses for SUBSTITUTE():
- Replacing dashes with slashes in dates
- Removing special characters like “$”, “#”, or “@”
- Standardizing text, e.g., change “Ltd.” to “Limited”
Quick Reference Cheat Sheet
- TRIM(text) – Removes extra spaces except for single spaces between words
- CLEAN(text) – Strips out non-printable characters
- SUBSTITUTE(text, old_text, new_text) – Replaces one value with another
- Combine Them:
=CLEAN(TRIM(SUBSTITUTE(A2, "@", ""))) - Extract digits only:
=REGEXREPLACE(A2, "[^0-9]", "")
Cleaning up data doesn’t have to be a manual headache. With TRIM(), CLEAN(), and SUBSTITUTE(), you can quickly tidy up messy cells, remove unwanted spaces or symbols, and make your spreadsheets ready for formulas and analysis. Whether you’re working on a contact list, financial report, or product catalog, these functions will keep your data looking clean and professional.
Start applying these simple cleanup tools in your Google Sheets and enjoy the satisfaction of a perfectly polished spreadsheet!