TRIM
Removes all extra spaces from text, leaving only single spaces between words.
TRIM(text)Arguments
textThe text to clean up
=TRIM(" Hello World ")Removes leading, trailing, and extra spaces
=TRIM(A1)Cleans up text from cell A1
=TRIM(A1)=TRIM(B1)Compare texts ignoring spaces
- •Removes spaces at start, end, and between words
- •Leaves single spaces between words
- •Use CLEAN to remove non-printable characters
- •Assuming TRIM only removes leading and trailing spaces - it also collapses multiple spaces between words down to one
- •Expecting TRIM to remove non-breaking spaces copied from a web page - those aren't regular spaces and TRIM leaves them alone
- •Running TRIM but not replacing the original data, so other formulas still reference the untrimmed version
CLEANRemoves non-printable characters that TRIM doesn't touch, useful together on messy pasted dataSUBSTITUTECan remove non-breaking spaces (CHAR(160)) that TRIM can't handle on its ownLENUseful for confirming TRIM actually shortened the text as expectedWhy doesn't TRIM remove all the extra spaces I see?
Some pasted text contains non-breaking spaces, common when copying from web pages, which look identical but aren't the character TRIM removes. Use SUBSTITUTE(A1,CHAR(160)," ") first.
Does TRIM remove spaces in the middle of text?
It reduces multiple consecutive spaces between words down to a single space, but doesn't remove the single spaces that belong there.
Do I need to replace the original data after using TRIM?
Yes - TRIM returns a new value in the formula cell, it doesn't modify the source cell. Copy and paste as values if you want to replace the original.
Need to translate a formula using TRIM?
Use our translator to convert your complete formula