TRIM in Excel: Remove Extra & Leading Spaces
Removes all spaces from text except for single spaces between words.
How to Use the TRIM Function in Excel
The TRIM function removes extra spaces from text, leaving only single spaces between words and eliminating leading/trailing spaces.
Syntax & Arguments
=TRIM(text)
- text (Required): The text or cell reference you want to remove extra spaces from.
Example: Cleaning Messy Text
| Original Text (A2) | Formula | Result |
|---|---|---|
| " John Doe " | =TRIM(A2) | "John Doe" |
| " SKU-1294 " | =TRIM(A3) | "SKU-1294" |
Pro Tip: Non-Breaking Spaces ( )
If TRIM does not remove spaces copied from websites, they are likely non-breaking spaces (ASCII 160). Use =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) to remove them.
Common Errors & Fixes
TRIM not removing all spaces
Causes:- Non-breaking spaces (CHAR(160)) are not removed by TRIM.
- Line breaks or tab characters between words.
Fixes:- Use SUBSTITUTE to replace CHAR(160) with space first, then TRIM.
- Use CLEAN before TRIM to remove line breaks.
Frequently Asked Questions
Does TRIM remove line breaks?
No, TRIM only removes extra spaces between words and leading/trailing spaces. Use CLEAN to remove non-printing characters and line breaks.
What is the difference between TRIM and CLEAN?
TRIM removes extra spaces. CLEAN removes non-printable characters (line breaks, tabs, etc.). Use both: =TRIM(CLEAN(A1)) for thorough cleaning.
Does TRIM work in Google Sheets?
Yes, TRIM works identically in Google Sheets with the same syntax.
Related Formulas
Explore related formula generators to solve similar problems
🛠️ Related Tools
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More