Trim function to remove excess space in MS Excel

Trim function to remove excess space in MS Excel

To demonstrate the formula's versatility, Excel provides a wide range of functions. Excel's Trim and Clean capabilities are once again Excel's most powerful. This feature is critical when it comes to preparing reports. The Trim and Clean tools are ideal for removing unnecessary data.

WHAT IS TRIM'S OBJECTIVE IN THE FIRST PLACE? Trim eliminates all but the spaces between words from the string.

To eliminate preceding and trailing punctuation and unnecessary whitespace, use Trim. It's the quickest and easiest approach to clean Excel's cells. After we use this feature, what do we get at the end? There are no additional spaces in the text that we receive

Syntax

In order to eliminate spaces from a piece of text, the Trim syntax is =TRIM(text). This formula is the simplest since it only requires one parameter to be placed into the formula.

 

In the video, you can see that the name entered in the B3 cell has a lot of gaps between it. The formula =TRIM(B3) may be used to remove this blank space, allowing you to display the name with just one space between the first and last names.

 

You may use the trim function to remove spaces from both individual cells and whole columns. In addition to trimming text, this feature removes the cell's numbers.

 

Also, in the video, you can see that the space put after the text indicates you that the cell has 11 characters, although what we see is just 7 characters in this example. This is because there is a gap following the text. The Trim formula can be used to get rid of this extra space.

Notes of Importance:

1: The trim command removes all whitespace, including those between words and those immediately following and immediately before them.

2: ASCII can only be trimmed to a maximum of 32 characters using the trim function.

3: The Trim function will not delete the HTML non-breaking space character.

The RTRIM Function:

The Rtrim function is also known as the right trim function. Excel VBA is required to make use of this feature. Using this method, you may eliminate any remaining spaces from a string. Right-side string spacing is eliminated by this method. Here, we use RTRIM instead of TRIM because the syntax is the same.

The LTRIM Function:

Left trim is the name of the Ltrim function. Excel VBA is the only way to utilise this function. This function removes the string's leading spaces. To the left of the string, the spaces are removed by this. LTRIM has the same syntax as Trim, except instead of TRIM, it is used.

Excel TRIM Function Not Working

When a formula contains non-printing characters with code values other than 32 and 160, the Excel trim function does not operate. Using either of the formulas, we can solve the problem.

Let's assume that cell B2 is experiencing some difficulties. Many other formulas will be used to fix these issues, including:

=CODE (LEFT (B2, 2)) is the leading space.

Space at the end of the code: =CODE

=CODE (MID (B2, n, 1)) in the nth place, where n is the problem's position in the string

Conclusion:

This is a completely automated process. Excel's built-in trim function may be used to remove unnecessary data. A worksheet function and VBA function are both available in Excel. Entering it in the formula field is simple. In 2000, it was first made available to the public. This function may be found under the String/Text function under the Formula tab.

Enjoyed this article? Stay informed by joining our newsletter!

Comments

You must be logged in to post a comment.

About Author
Recent Articles