The Excel TRIM function, explained interactively.
TRIM strips leading and trailing spaces from a text value and collapses runs of internal spaces to exactly one. Deceptively simple — its big gotcha is what it doesn’t touch: tabs, line breaks, and non-breaking spaces all survive.
How to use TRIM
- Type
=TRIM(and point at the text — usually a cell reference to the messy imported column. - Close the parenthesis. TRIM returns the cleaned text: no leading spaces, no trailing spaces, internal runs collapsed to one.
- If TRIM doesn’t seem to do anything, the invisible characters are probably not ASCII spaces. Try
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))for web-copy-paste non-breaking spaces. - For a full cleanup of imported data, chain with CLEAN:
=TRIM(CLEAN(A2))removes control characters first, then normalises whitespace.
The demo uses middle-dot (·) for visible spaces and »» for tabs. Row 6 shows what TRIM doesn’t touch — the tab survives because it’s not a space.
CHAR(160)), or other whitespace.| A | B | |
|---|---|---|
| 1 | Raw (· = space, »» = tab) | Trimmed |
| 2 | ····Apple | Apple |
| 3 | Banana···· | Banana |
| 4 | Cherry·····pie | Cherry pie |
| 5 | ···Durian··· | Durian |
| 6 | Eggplant | Eggplant |
| 7 | ··Fig»»»»·jam· | Fig jam |
=TRIM(A2). TRIM strips leading & trailing spaces and collapses internal runs to one space. Note row 6 (“Fig\t\t jam”): the tabs survive because Excel’s TRIM only removes the ASCII space character (CHAR(32)) — not tabs or CHAR(160).TRIM syntax and arguments
One required argument. See Microsoft’s official TRIM reference for the canonical specification.
TRIM examples
Four patterns that cover nearly every real TRIM use.
Example 1: TRIM for a CSV-import column
Quick cleanup of a column of names or SKUs after a CSV paste.
Strips leading/trailing spaces and collapses internal runs. The workhorse usage — apply to a helper column, then paste-special-values over the source to lock in the cleaned data.
Example 2: TRIM + SUBSTITUTE for non-breaking spaces
Data copied from the web often has CHAR(160) (NBSP) instead of ASCII spaces. TRIM alone won’t clean it.
SUBSTITUTE converts every non-breaking space to a regular space first; TRIM then strips them as expected. Single most useful TRIM variant in real-world dashboards.
Example 3: TRIM + CLEAN for full cleanup
For data from PDFs or legacy systems that carries control characters alongside extra spaces.
CLEAN strips non-printable ASCII (CHAR(0)–CHAR(31)), which includes tabs and line breaks. TRIM then normalises the remaining whitespace. Standard pre-processing for messy imports.
Example 4: TRIM before a lookup
Whitespace is the #1 cause of VLOOKUP and MATCH returning #N/A on data that visually matches.
TRIM normalises the lookup value before the match. Better: normalise the catalog column too — a leading space in the target also kills the match. Most reliable pattern: run TRIM once on the catalog as a prep step, then never worry about it in later formulas.
Common TRIM errors and fixes
Four failure modes, each with what to check and how to recover.
TRIM doesn’t clean the spaces it should
Cause: the “spaces” aren’t ASCII spaces. Non-breaking spaces (CHAR(160)), tabs, and other whitespace characters survive TRIM untouched.
Check the character code: =CODE(MID(A2, 1, 1)). If it returns 160 (NBSP), use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). For 9 (tab) or 10 (LF), wrap with CLEAN.
TRIM returns #VALUE!
Cause: the argument is an array or error value. TRIM expects a scalar text value.
Point at a single cell or wrap the source in IFERROR. On Excel 365 with dynamic arrays, TRIM(A2:A100) spills and works on each cell — older Excel needs it in a helper column.
TRIM output won’t add to numbers
Cause: TRIM always returns text. EvenTRIM(" 123 ") returns the string "123", not the number.
Wrap with VALUE(): =VALUE(TRIM(A2)) returns a real number. Or multiply by 1 for the shorter idiom: =TRIM(A2)*1.
TRIM breaking intentional spacing
Cause: you needed multiple spaces (code indentation, poetry, artistic formatting). TRIM collapses them.
Don’t use TRIM for layout-sensitive text. If you need end-strip without internal collapse, combine twoSUBSTITUTE calls keyed to specific patterns, or use a formula like =MID(A2, FIND("?", SUBSTITUTE(A2, " ", "?", 1)), LEN(A2)).
TRIM vs CLEAN, SUBSTITUTE & TEXTSPLIT
Four text-cleanup tools. Pick based on what kind of unwanted character you’re removing and whether you need to restructure, not just strip.
| Function | What it removes | Args | Typical use |
|---|---|---|---|
| TRIM | ASCII spaces (leading, trailing, internal runs) | text | CSV/paste cleanup for lookup keys |
| CLEAN | Non-printable control chars (0–31) | text | PDF/legacy-system imports with tabs, CRs |
| SUBSTITUTE | Any specific character or substring you name | text, old, new, [instance] | NBSP, smart quotes, regional separators |
| TEXTSPLIT | Splits rather than strips | text, col_delim, [row_delim] | Turning a CSV cell into separate columns |
Rule of thumb: TRIM for spaces, CLEAN for control chars, SUBSTITUTE for anything you can name specifically. Chain them for heavy cleanup: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) handles 95% of import cases in one formula.
TRIM frequently asked questions
6.01Does TRIM remove tabs and line breaks?▸
No. Excel’s TRIM only removes the ASCII space character (CHAR(32)). Tabs (CHAR(9)), line breaks (CHAR(10), CHAR(13)), and non-breaking spaces (CHAR(160)) all survive. Use CLEAN to strip control characters (CHAR(0)–CHAR(31)), and SUBSTITUTE(text, CHAR(160), “ ”) before TRIM for non-breaking spaces.
6.02Why does TRIM not remove spaces from my imported data?▸
Because the “spaces” aren’t ASCII spaces — they’re non-breaking spaces (CHAR(160)), common in web-copy-paste and HTML imports. Chain: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) converts NBSP to ASCII space first, then TRIM strips as expected.
6.03Does TRIM work on numbers?▸
Yes, but it coerces numbers to text first and returns text. =TRIM(123) returns the string "123", not the number 123. If downstream formulas need a number, wrap with VALUE() or multiply by 1.
6.04How do I collapse multiple internal spaces to one?▸
TRIM handles it automatically. =TRIM("Hello world") returns "Hello world" with exactly one space between the words. Internal runs of any length collapse to a single space; leading and trailing ends are stripped entirely.
6.05What’s the difference between TRIM and CLEAN?▸
TRIM handles spaces; CLEAN handles non-printable control characters (CHAR(0)–CHAR(31)) like tabs, line breaks, form feeds. For the full cleanup pass on imported data, chain them: =TRIM(CLEAN(A2)) strips control chars first, then normalises whitespace.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.