Ever typed two identical-looking values in Google Sheets and they didn’t match? That’s often due to invisible troublemakers: leading/trailing spaces or non-printing characters like line breaks or tabs. These small glitches can mess up sorting, filtering, VLOOKUPs, and data comparisons. Luckily, Google Sheets has a few simple formulas to clean them up. If you’re just starting out with spreadsheets, this guide will help you fix your data quickly and confidently.
What Are Leading/Trailing Spaces and Non-Printing Characters?
These are hidden characters that don’t show up when you look at a cell but affect how Google Sheets processes your data:
- Leading/trailing spaces: Extra spaces before or after the actual content.
- Non-printing characters: Things like line breaks (ENTER), tab characters, or non-breaking spaces (from copied content).
They often appear when copying data from websites, PDFs, emails, or forms.
Real-Life Scenario: Clean Up a Contact List
Imagine you’re building an email list, and your data looks like this:
| Name | |
|---|---|
| John Smith | john.smith@example.com |
| Maria Garcia | maria.garcia@example.com |
| Lee Chen |
lee.chen@example.com |
The extra spaces and breaks will break formulas and validations. Let’s clean them!
Step-by-Step: Clean Text with Formulas
Step 1: Use the TRIM() Function
- TRIM removes all extra spaces — except single spaces between words.
- Example:
=TRIM(A2)
Step 2: Use the CLEAN() Function
- CLEAN removes line breaks and non-printing characters.
- Example:
=CLEAN(A2)
Step 3: Combine Both for Best Results
Use both TRIM and CLEAN together:
=TRIM(CLEAN(A2))Example Output
| Original Name | Cleaned Name | Original Email | Cleaned Email |
|---|---|---|---|
| John Smith | John Smith | john.smith@example.com | john.smith@example.com |
| Maria Garcia | Maria Garcia | maria.garcia@example.com | maria.garcia@example.com |
| Lee Chen |
LeeChen | lee.chen@example.com | lee.chen@example.com |
Pro Tips
- Use ARRAYFORMULA: To apply cleanup across an entire column:
=ARRAYFORMULA(TRIM(CLEAN(A2:A))) - Paste as values: Once cleaned, copy and paste the cleaned results as “values only” to overwrite the originals.
- Watch for no-break spaces: If TRIM/CLEAN miss something, use
SUBSTITUTEto remove Unicode characters:=SUBSTITUTE(A2, CHAR(160), "")
Key Benefits
- Fix broken filters, formulas, and dropdowns.
- Ensure accurate data comparisons and lookups.
- Clean data for exports, mailing lists, or reports.
Quick-Reference Cheat Sheet
| Task | Formula |
|---|---|
| Remove extra spaces | =TRIM(A2) |
| Remove non-printing characters | =CLEAN(A2) |
| Do both at once | =TRIM(CLEAN(A2)) |
| Remove non-breaking spaces | =SUBSTITUTE(A2, CHAR(160), "") |
Cleaning up invisible characters in your spreadsheet may not sound exciting, but it’s a game-changer for anyone working with real-world data. Whether you’re building a report, cleaning a contact list, or just trying to make your formulas work, using TRIM() and CLEAN() can save you hours of confusion. Try it out in your next project, and enjoy the power of tidy, reliable data!