Featured image
TRIM in Excel
TRIM strips the spaces from both ends of a piece of text and collapses any run of spaces inside it down to one: =TRIM(text). It is the standard repair for imported data, and the first thing to try when two values look identical on screen and refuse to match.
=TRIM(text)
A trailing space is the most expensive invisible character in spreadsheet work. It survives copy and paste, it survives a CSV export, it does not show up in the cell, and it makes two values that look the same compare as different. A lookup against it returns #N/A, a COUNTIF against it returns zero, and nothing on screen explains why.
TRIM is the fix, and it is worth running over any column that arrived from somewhere else before doing anything with it.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The text to clean, usually a cell reference. A number handed to TRIM comes back as text that looks like the number, which is occasionally a trap of its own.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
What TRIM actually removes
=TRIM(" North Star ")Returns North Star
Both ends stripped, and the double space in the middle collapsed to one. That middle behaviour is the part people do not expect: TRIM is not only a leading-and-trailing function.
Proving the spaces were there
=LEN(" North ")Returns 9
Nine characters for a five-letter word. LEN is how you confirm a suspicion about spaces, because the cell itself will not tell you.
And proving TRIM fixed it
=LEN(TRIM(" North "))Returns 5
Five. Running LEN before and after TRIM on a real column is the quickest way to find out how much of a problem you have.
A lookup rescued
=VLOOKUP(TRIM(" Chen "), A2:D7, 4, FALSE)Returns 7600
Without the TRIM, the same formula returns #N/A: there is a Chen in the table and no space-Chen-space. This single line is why TRIM appears in the troubleshooting section of every lookup page on this site.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Diagnosing a space problem
Before cleaning anything, confirm that spaces really are the cause. Two checks settle it in under a minute.
=" Chen "=A4Returns FALSE. A direct comparison. If this is FALSE while the two cells look identical, something invisible differs.
=TRIM(" Chen ")=A4Returns TRUE. The same comparison with TRIM applied. TRUE confirms the difference was whitespace and nothing stranger.
Cleaning a column rather than wrapping every formula
Wrapping every lookup in TRIM works, and it means carrying the problem around forever. Cleaning the column once is better.
- 1Put =TRIM(A2) in a spare column beside the data and copy it down.
- 2Check a few rows with LEN to confirm the lengths changed where you expected.
- 3Copy the new column and paste it back over the original as values, not formulas.
- 4Delete the helper column.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=TRIM(" North ") + 1WhyTRIM returns text. Adding text to a number is the error, not the TRIM itself.
FixIf the column holds numbers that arrived as text, TRIM alone does not convert them. Wrap the result in VALUE: =VALUE(TRIM(A2)).
=TRIM(North)WhyLiteral text without quotation marks.
FixQuote it, or point at a cell: =TRIM(B2).
What goes wrong
These are the mistakes that come up again and again with TRIM. Each one is reasonable. That is why people make it.
Expecting TRIM to remove every space.
WhyThe name suggests tidying, and most of the time the spaces being removed are at the ends.
InsteadIt keeps single spaces between words, which is what you want for names and addresses. To remove every space including those, use SUBSTITUTE(A2, " ", "").
Assuming a failed match must be something more complicated.
WhySpaces are invisible, so they are the last thing anybody suspects.
InsteadCheck LEN on both values before anything else. It takes ten seconds and resolves a surprising proportion of lookup problems.
Leaving the TRIM wrapped around every formula forever.
WhyIt works, and the sheet is finished.
InsteadClean the source column once and paste the result back as values. Otherwise every new formula written against that column has to remember the wrapper.
Using TRIM on a column of numbers.
WhyThe numbers look messy, so cleaning them seems sensible.
InsteadTRIM returns text. A number that goes through it comes back as text and stops adding up. Use VALUE around it if the result has to be numeric.
Common questions
Why is my VLOOKUP returning #N/A when the value is clearly in the table?
Most often a trailing space on one side. Compare the two cells directly with an equals formula: if it says FALSE while they look identical, wrap one in TRIM and compare again. If that fixes it, clean the column.
Does TRIM remove all spaces in Excel?
No. It removes leading and trailing spaces and reduces runs of internal spaces to a single space. To strip every space, use SUBSTITUTE(A2, " ", "").
Why does TRIM not fix my data?
Because the character is probably not an ordinary space. Text pasted from a web page often contains a non-breaking space, character 160, which TRIM ignores. Substitute it for a real space first, then TRIM.
How do I remove spaces without a helper column?
You cannot, directly: a formula cannot overwrite the cell it reads from. Build the cleaned column beside the data, then paste it back over the original as values and delete the helper.