What is the Excel TRIM Function?
The excel TRIM function removes extra spaces, both in front, in the middle or behind, except for space that separate two words. So the TRIM function can do LEFT TRIM, RIGHT TRIM or “MIDDLE TRIM” simultaneously in one function.
TRIM Syntax
TRIM(text)
text, the text from which to remove the extra spaces.
Usage Note:
- The TRIM function removes the extra spaces in all positions, left trim, right trim or middle trim.
- The TRIM function only removes the extra spaces, to remove non-printing characters use the CLEAN function.
How to Use TRIM Function in Excel
For example, there is data as shown below. Column A contains a text; column B calculates the length of the text in column A. Column D is the result of the TRIM function (and the CLEAN function), and column E calculates the text length of the TRIM function result.
Excel Left Trim
The Formula
=TRIM(A2)
Cell A2 has extra space on the left (Excel Left Trim), TRIM function removes all extra spaces and text length in cell D2 becomes 15.
Excel Right Trim
The Formula
=TRIM(A3)
The extra space on the right is not visible, but from the length of the text, you know that the extra spaces in cell A3 are equal to cell A2. In appearance, there is no difference between cell A3 and D3, but the character length indicates if the extra spaces on the right have been deleted (Excel Right Trim).
Excel Middle Trim
The Formula
=TRIM(A4)
The number of extra spaces in cell A2, A3 and A4 are equal. The extra spaces in cell A4 are in the middle. After the extra spaces are removed, the text length becomes 15.
Excel Left, Middle, and Right Trim
The Formula
=TRIM(A5)
Cell A5 has extra spaces on left, middle and right. The TRIM function removes extra spaces wherever they are. The result is the length of the text to be 15.
Trim Non-Printable Character
The Formula
=TRIM(A6)
Cell A6 contains extra spaces and line break character. The TRIM function only removes the extra spaces not including the line break (non-printable character), although the appearance is the same as the TRIM function result above, the character length is different.
The Formula
=CLEAN(A7)
Cell A7 contains the same character as cell A6. The CLEAN function only removes the line break character, not the extra space.
The Formula
=TRIM(CLEAN(A8))
To removes the extra space and line breaks simultaneously, use the TRIM and CLEAN functions together. The result is a character length of 15.
