Excel has many different functions to showcase the power of the formula. The trim function and Clean function are again the two most powerful functions in Excel. This function is of utmost importance while making reports. The Trim and Clean function are the perfect functions for cleaning up the data.
The question that comes to your mind is WHAT DOES TRIM DOES? The trim function removes all the spaces from the string except the space between words.
Trim will help you to remove leading, trailing, and extra space between the words. It is the fastest and easiest way to clean spaces in Excel. WHAT DO WE GET AFTER WE USE THIS FUNCTION? We get the text with extra spaces removed.
Syntax
The Syntax for the Trim is =TRIM(text) where the text stands for the text from which you want to remove spaces. It is the simplest formula as this formula requires only one parameter to be entered into the formula.
Example of Trim function: In the video, you can see that the name entered in the B3 cell has many spaces in between the same. You can clean this space by trimming this space using the formula =TRIM(B3) and it will show the name with only one space between the names.
The trim function can help you to trim spaces not for a single cell but also for columns. It not only trims text but also the numbers in the cell.
In the video, you also see that the space entered after the text shows you that the cell contains 11 characters whereas what we see is 7 characters. This is due to the space entered after the text. We can remove this space by using the Trim formula.
Important Notes:
1: The trim function clears the extra space between words and also deletes the space after and before the words.
2: The trim function only removes 32 characters from ASCII.
3: The non-breaking space character that contains in HTML cannot be removed by the Trim function.
Difference between Clean and Trim function in Excel
Both functions are similar to each other. However, the only difference between this function is that the Trim function removes additional space whereas the clean function removes the unprintable characters.
RTRIM function:
Rtrim function is known as the Right trim function. This function can be used only in Excel VBA. This function is used to remove trailing spaces from the string. It removes the spaces from the right side of the string. Here the Syntax is the same as the Trim function; instead of TRIM, we used RTRIM.
LTRIM function:
Ltrim function is known as the Left trim function. This function can be used only in Excel VBA. This function is used to remove leading spaces from the string. It removes the spaces from the left side of the string. Here the Syntax is the same as the Trim function; instead of TRIM, we used LTRIM.
Excel TRIM Function Not Working
The Excel trim function does not work when the formula contains some specific non printing characters with code values other than 32 and 160. In such a case, we use either of the formulae to find the problem
Let us suppose that cell B2 has some problems. We will rectify these problems using a various formula like:
Leading space: =CODE (LEFT (B2, 2))
Trailing space: =CODE (RIGHT (B2, 2))
In between space: =CODE (MID (B2, n, 1))) where n is the position of the problem in the string.
Conclusion:
This function is fully automatic. The trim function is an in-built function in Excel. It is used as a worksheet function and a VBA function in Excel. It is entered in the formula bar. It was introduced in the year 2000. You find this function in the formula tab in the String/Text function.