FormuladrillTRIM
TRIM(text) removes all spaces from the start and end of a text string and reduces runs of spaces inside it to a single space. It fixes the invisible trailing spaces that make VLOOKUP and COUNTIF miss values. It does not remove non-breaking spaces (CHAR(160)) from web pages; use SUBSTITUTE for those.
Syntax
TRIM(text)| Part | What it is |
|---|---|
text | The cell or string to clean. |
Examples
=TRIM(" Jane Doe ")Result: Jane Doe
=LEN(A2) - LEN(TRIM(A2))Result: How many extra spaces A2 contains (0 when it is clean)
=VLOOKUP(TRIM(E2), A:C, 3, FALSE)Result: The lookup works even when E2 was pasted with a trailing space
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))Result: A2 with non-breaking spaces converted and then trimmed
Data copied from websites often contains CHAR(160).
Common mistakes
- TRIM seems to do nothing: the spaces are non-breaking (CHAR(160)) or the cell contains line breaks (CHAR(10)). Use SUBSTITUTE for 160 and CLEAN for line breaks.
- Trimming a number turns it into text; wrap in VALUE if the column is numeric.
- Putting TRIM in a helper column and then forgetting to replace the original: copy the helper and Paste Special, Values over the source.
Practice TRIM
These drills check your answer on live data, free, no account needed.
- Clean up namesThe names were typed carelessly. Remove extra spaces and give each word a capital first letter. Fill B2 and B3.
Questions
How do I remove spaces in Excel?
=TRIM(A2) removes leading, trailing and double spaces. To remove every space including single ones inside, use =SUBSTITUTE(A2, " ", "").
Why does TRIM not remove the spaces?
They are probably non-breaking spaces (code 160) rather than normal spaces (code 32). Use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
Related
- SUBSTITUTEHow the Excel SUBSTITUTE function replaces one piece of text with another, with examples for removing dashes, swapping the nth occurrence and counting commas.
- PROPER, UPPER, LOWERHow to change the case of text in Excel with PROPER, UPPER and LOWER, with examples for cleaning names and emails, TRIM, and the names PROPER gets wrong.
- LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.
- VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.