TRIM: clean ordinary spaces before a lookup
A cell can display the right customer ID and still fail a match because it contains extra spaces. TRIM is a useful first check, but it does not remove every imported character.
TRIM handles ordinary spaces and collapses repeated internal ordinary spaces into one. It does not remove every Unicode whitespace character. SUBSTITUTE targets the specific nonbreaking space in this fixture; using UNICHAR makes the intended Unicode character explicit.
| Formula | Expected result / interpretation |
|---|---|
| =LEN(A2) | 9 |
| =TRIM(A2) | P-101, length 5 |
| =LEN(TRIM(A3)) | 7 — nonbreaking spaces remain |
| =TRIM(SUBSTITUTE(A3,UNICHAR(160),” “)) | P-101, length 5 |
| Cell | Enter |
|---|---|
| A2 | =” P-101 “ |
| A3 | =UNICHAR(160)&”P-101″&UNICHAR(160) |
Create the sample inputs using these formulas so the invisible characters are reproducible.
Clean web-copy spaces before matching an ID
Formula syntax
=TRIM(text)
What goes into it
text
Text whose ordinary extra spaces should be normalized.
A quick example first
=TRIM(" CellLingo tools ")
Returns “CellLingo tools”.
=TRIM(A2)
Removes leading/trailing ordinary spaces and collapses repeated spaces.
Before you copy this into a real sheet
Clean lookup keys on both sides of a comparison, or create a reviewed normalized key column. Do not remove every space from an address or name merely to force a match: spaces can carry meaning. CLEAN helps with some control characters but is not a complete Unicode sanitizer. Inspect suspicious characters before extending the cleaning rule. Keep raw imported values available so accidental changes can be reversed and duplicate IDs introduced by normalization can be identified.
Useful habits
- TRIM is useful for imported data before lookups.
- Non-breaking spaces from web pages may require SUBSTITUTE as well.
- Use CLEAN when control characters are also present.
Where people get caught out
- Assuming TRIM removes every Unicode whitespace character.
- Cleaning displayed text but forgetting that the original dirty field is still used in the lookup.
Other functions worth a look
A detail worth remembering
Standard TRIM may not remove nonbreaking spaces from web or PDF copies. When a value still refuses to match, inspect its character codes rather than trimming repeatedly.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.