Clean Up Text with TRIM, CLEAN, and SUBSTITUTE (Spaces, Line Breaks, and Special Characters in 1 Minute)
The real reason a report looks messy is not the values—it is the spaces and characters. With the three-function combination of TRIM, CLEAN, and SUBSTITUTE, you can create clean labels no matter where the text was copied from.
Basic Concepts and Differences
| Function | Purpose | Key Point |
|---|---|---|
| TRIM | Removes leading and trailing spaces; reduces duplicate spaces to one | Works only with regular spaces (32), not NBSP (160) |
| CLEAN | Removes nonprinting characters | Line breaks such as CR/LF may remain depending on the situation |
| SUBSTITUTE | Replaces text strings | Standardizes specific characters/delimiters and converts NBSP to spaces |
6 Essential Recipes
1) Clean up spaces and line breaks pasted from the web in one step
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))2) Remove line breaks and make text a single line
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," "))3) Remove all spaces (for IDs and codes)
=SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(160),"")4) Standardize delimiters (slash → hyphen)
=SUBSTITUTE(A2," / ","-")5) Remove text in parentheses
=TRIM(SUBSTITUTE(A2, "(" & MID(A2,FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1) & ")", ""))6) Extract numbers only (Microsoft 365)
=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1),""))(For older versions, use the ROW($1:$99) pattern as an array formula.)
Bulk Cleaning Template
LET recommended Store repeated references in variables to reduce calculation workload.
=LET(t, A2,
t1, SUBSTITUTE(t,CHAR(160)," "),
t2, SUBSTITUTE(SUBSTITUTE(t1,CHAR(13)," "),CHAR(10)," "),
TRIM(CLEAN(t2)))Enter the formula once in an Excel Table column and let it fill automatically for easier maintenance.
Common Mistakes & Performance Tips
- NBSP remains when using TRIM alone → Always use
SUBSTITUTE(,CHAR(160)," ")first. - Missing line-break cleanup → Replace both CR (13) and LF (10).
- Replacement order → The safest order is replace → TRIM → CLEAN.
- Excessive full-column references → Use precise ranges or table references.
Summary
| Goal | Formula |
|---|---|
| All-in-one NBSP/space/line-break cleanup | TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) |
| Make text a single line | TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," ")) |
| Remove all spaces | SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(160),"") |
| Standardize delimiters | SUBSTITUTE(A2," / ","-") |
FAQ
What is the difference between REPLACE and SUBSTITUTE?
REPLACE replaces text based on position and length, while SUBSTITUTE finds and replaces specific text. SUBSTITUTE is more suitable for variable-length replacements.
I want to remove only leading and trailing spaces while keeping internal spaces.
=TRIM(A2) also reduces consecutive internal spaces to one. To preserve internal spaces, you need a separate UDF or pattern-based approach that removes only leading and trailing spaces.