Formula guide · Excel and Google Sheets · Updated May 13, 2026
Text Cleanup Formulas
Clean imported spreadsheet text with TRIM, CLEAN, SUBSTITUTE, LEFT, RIGHT, and TEXTSPLIT.
Quick Answer
Start with TRIM and CLEAN for extra spaces and non-printing characters, then use SUBSTITUTE or split formulas for deeper cleanup.
Copyable Formula
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))Syntax
=TRIM(CLEAN(A2))Excel and Google Sheets
Worked Example
| A | B |
|---|---|
| Raw text | Cleaned text |
| Acme Corp | Acme Corp |
| North Co | North Co |
Result: Removes normal spaces, non-printing characters, and non-breaking spaces.
Steps
- Use CLEAN for non-printing characters.
- Use SUBSTITUTE for characters CLEAN does not catch, such as non-breaking spaces.
- Use TRIM last to normalize spaces.
Common Mistakes
- Assuming TRIM removes every invisible character.
- Cleaning only one sample row instead of testing imported edge cases.
- Overwriting source data before checking the cleaned output.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.