TRIM in Excel: Syntax, Examples and Errors
Updated 2026-10-15 · Free · Works in Excel & Google Sheets
Syntax
=TRIM(text)Quick answer
TRIM cleans text of stray spaces: =TRIM(A2) removes leading and trailing spaces and collapses inner runs to a single space, turning " jane doe " into "jane doe". It removes only the space character — non-breaking spaces and line breaks survive — and the result is always text.
Syntax
=TRIM(text)| Argument | What it means |
|---|---|
| text | The cell or literal to clean. Required — and the only argument. No options for which characters to remove: TRIM always targets the space character (U+0020) and nothing else. |
A single argument means no separators to localize: =TRIM(A2) looks identical in every Excel language, semicolon or comma.
TRIM does two jobs at once: it deletes spaces at the start and end of the text entirely, and it shrinks any run of inner spaces to exactly one. So " jane doe " becomes "jane doe" — note the words stay separated by one space, not glued together.
Basic examples
=TRIM(A2)The everyday fix for pasted or imported data: names like " jane doe " become "jane doe", and lookups stop failing because a trailing space made "AE-041 " unequal to "AE-041".
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))The rescue formula for web-scraped data: CHAR(160) is the non-breaking space, which TRIM cannot see. Converting it to a normal space first lets TRIM finish the job — this one line fixes most "TRIM did nothing" cases.
=UPPER(TRIM(A2))Clean-then-normalize: TRIM strips the padding, UPPER standardizes the letters. Nesting in this order matters — clean first, convert second — and the same pattern works with PROPER for display names.
=LEN(A2)-LEN(TRIM(A2))The space counter: the length difference tells you exactly how many characters TRIM removed. A 2 means two stray spaces, which is the fastest way to audit a column before committing the cleanup.
=IF(TRIM(A2)=TRIM(B2), "Match", "Diff")The forgiving comparison: matching both sides after TRIM means " AE-041 " and "AE-041" finally compare equal. Note Excel’s plain = already ignores nothing here — spaces count — so TRIM on both sides is the standard fuzzy match for keys.
TRIM stringifies numbers: TRIM(12345) returns the text "12345" that no longer sums. If the column was imported as text, pair the cleanup with VALUE: =VALUE(TRIM(A2)) — spaces around a number are the classic reason VALUE errors out.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| TRIM seems to do nothing | The padding is non-breaking spaces (U+00A0) from web pages — TRIM only removes the plain space (U+0020). | Convert first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). |
| Inner spaces stay doubled | By design TRIM keeps one space between words and only collapses runs — it never glues words together. | To remove every space, use =SUBSTITUTE(A2, " ", "") instead. |
| The result cannot be summed | TRIM always returns text — a numeric-looking "12345" is a label. | Convert with =VALUE(TRIM(A2)) so digits land back as numbers. |
| Line breaks survive the cleanup | TRIM targets only the space character; CHAR(10) line breaks are untouched. | Add CLEAN for non-printables or =SUBSTITUTE(A2, CHAR(10), " ") for line breaks. |
| Blank rows stop being blank | TRIM of an empty cell returns "" — empty text, which is no longer blank for ISBLANK. | Guard it: =IF(A2="", "", TRIM(A2)) keeps genuinely empty rows empty. |
Not an error, but worth knowing: TRIM is a space specialist. It will not touch tabs (CHAR(9)), line breaks (CHAR(10)), non-breaking spaces (CHAR(160)) or any other invisible character — stack CLEAN or SUBSTITUTE for those.
TRIM vs. CLEAN and SUBSTITUTE
| TRIM | CLEAN | SUBSTITUTE | |
|---|---|---|---|
| Targets | The space character only (U+0020) | Non-printable characters 1–31 (line breaks, tabs) | Anything you name — any character or substring |
| Behavior | Deletes at the edges, collapses inner runs to one | Deletes outright | Replaces with what you choose — including "" |
| Typical job | Pasted-in padding around words | Line breaks inside cells | Non-breaking spaces, targeted removals |
| Blind spot | U+00A0, line breaks, tabs | U+00A0 is above 31, so it survives | None — but you must know what to replace |
The practical order for dirty imports: SUBSTITUTE(CHAR(160), " ") first, then TRIM, then CLEAN if line breaks remain. Each function covers the others’ blind spots, which is why they almost always appear nested together.
TRIM vs. Flash Fill
| TRIM formula | Flash Fill (Ctrl+E) | |
|---|---|---|
| Output | A live formula in a helper column; recalculates when the source changes | Static values, written once; new rows need a refill |
| Precision | Deterministic — every space, every row | Pattern-guessed — spot-check the result column |
| Best for | Repeating imports and reports that must stay current | One-time cleanups of a finished table |
| Available in | Every Excel version and Google Sheets | Excel 2013 and later; Sheets answers with Smart Fill |
Choose by lifespan: TRIM formulas for data that keeps arriving, Flash Fill for a one-time reshaping you will paste over. If you keep the formula route, paste values back once the column is final.
TRIM in Google Sheets
- Same name, same single argument — every formula above pastes into Sheets unchanged.
- Sheets also offers a menu command Data → Data cleanup → Trim whitespace that writes static values, like Flash Fill.
- The non-breaking-space blind spot is identical: SUBSTITUTE(CHAR(160), " ") works the same way in Sheets.
- The LEN difference trick (=LEN(A2)-LEN(TRIM(A2))) transfers as-is for auditing columns.
Limitations and pro tips
- One job per call: TRIM handles spaces only — tabs, line breaks and non-breaking spaces need CLEAN or SUBSTITUTE stacked around it.
- Nested runs of CHAR(160) plus spaces are common on web data: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) is the single most reused cleanup formula.
- Audit before you commit: =LEN(A2)-LEN(TRIM(A2)) shows how many characters each row sheds, so you can spot outliers before pasting values back.
- Pair with VALUE for numbers: =VALUE(TRIM(A2)) clears the padding that makes digit text refuse to convert.
- Paste values back: Copy the helper column → Paste Special → Values → delete the helper, so the table survives source-column cleanup.
Think of TRIM as the first station of a cleaning line: convert the invisible padding, TRIM the spaces, CHECK with LEN, then paste values back. Each stage in its own column keeps the recipe auditable — and one formula away from a fix when the format shifts.
Related Tools
Related Functions
Frequently Asked Questions
What does the TRIM function do in Excel?
It removes stray spaces: leading and trailing spaces are deleted entirely, and any run of inner spaces collapses to a single space. =TRIM(A2) turns " jane doe " into "jane doe". The result is always text.
Why doesn’t TRIM remove all the spaces?
By design TRIM keeps one space between words — it only collapses runs and strips the edges. To remove every space, use SUBSTITUTE: =SUBSTITUTE(A2, " ", "") replaces all spaces with nothing.
Why does TRIM do nothing on my data?
The padding is almost certainly non-breaking spaces (CHAR(160)), which TRIM cannot see. Convert them first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). This is the standard fix for web-copied data.
Does TRIM remove line breaks?
No — TRIM targets only the space character (U+0020). Line breaks are CHAR(10) and survive; remove them with =SUBSTITUTE(A2, CHAR(10), " ") or CLEAN for the other non-printables.
Does TRIM turn numbers into text?
Yes — TRIM always returns text, so a cleaned "12345" no longer sums. Wrap the result in VALUE: =VALUE(TRIM(A2)) converts it back, and the TRIM-first order also fixes the stray-space errors VALUE would otherwise throw.
How do I count how many spaces TRIM removed?
=LEN(A2)-LEN(TRIM(A2)) returns exactly that number per cell. A 0 means nothing to clean, a 2 means two stray spaces — the quickest audit before pasting values back.
Does TRIM work in Google Sheets?
Yes — same name, same single argument, same behavior. Sheets also has Data → Data cleanup → Trim whitespace as a menu alternative, and the same SUBSTITUTE(CHAR(160)) rescue applies there.
Ready to try it on your own file?
Open Trim Whitespace in Excel — It's Free