Trim function to remove excess space in Excel

TRIM Function

Excel has many different functions to the showcase the power of formula. Trim function and Clean function are again the two most powerful function in Excel. This function is of utmost importance while making reports. The Trim and Clean function are the perfect function 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 the 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 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 for Trim function: In the video you can see that the name entered in 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.

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: Trim function clears the extra space between words and also deletes the space after and before the words.

2: Trim function only removes 32 characters from ASCII.

3: The non-breaking space character that contains in HTML cannot be removed by Trim function.

Difference between Clean and Trim function in Excel

Both the functions are similar to each other. However the only difference between this function is that Trim function removes additional space whereas 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 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 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 case we use either of the formula to find the problem

Let us suppose that cell B2 has some problems. We will rectify this problems using 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.

 

Hope you like our tutorial for more cool and amazing trick of excel Like our Excel Superstar Facebook Page and subscribe to our Excel Superstar YouTube channel. Excel Superstar is the leading online training company, which provides Online Excel Course in Hindi. Connect with us and become an Excel Superstar.