TRIM
Strip leading and trailing spaces, and collapse runs of spaces inside text, so values compare as equal.
Syntax
TRIM(text)| Argument | Required | Description |
|---|---|---|
text | Required | The text to clean, usually a cell reference rather than a literal. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=TRIM(" Bo ") | Bo | Leading and trailing spaces removed. |
=LEN(TRIM(" Bo ")) | 2 | Proof the padding is gone rather than merely invisible. |
=TRIM("A B") | A B | Runs of spaces inside the text collapse to one. |
=VLOOKUP(TRIM(" Bo "),A2:B3,2,FALSE) | 1 | The same lookup without TRIM returns #N/A. |
Why a lookup fails on data that looks identical
A trailing space is invisible on screen and fatal to an exact match. "Bo " and
"Bo" are different strings, so a lookup for one against the other returns
#N/A while both cells appear to say the same thing.
=VLOOKUP(" Bo ", A2:B3, 2, FALSE) → #N/A
=VLOOKUP(TRIM(" Bo "), A2:B3, 2, FALSE) → 1This is overwhelmingly common in data exported from other systems, where a fixed-width field leaves padding behind.
Clean the column, not every formula
Wrapping the lookup value works, but it only fixes the side you wrapped. If the padding is in the table, every formula reading that table needs the same treatment forever.
Cleaning the source column once with TRIM and using the cleaned version is
almost always the better trade — one operation instead of a rule everybody has
to remember. The six usual causes of a failing
lookup covers where else to look when it isn’t spaces.
What it does not remove
TRIM handles ordinary spaces. It does not remove tabs, line breaks, or the
non-breaking spaces that arrive with text pasted from a web page — those look
identical and survive it. If a value still refuses to match after trimming,
compare LEN on both sides; a length difference you cannot see is the tell.
Excel compatibility
Matches Excel, including collapsing internal runs of spaces to a single space.