TRIM FUNCTION v/s CLEAN FUNCTION in MS Excel

Hi everyone, Today we are going to discuss TRIM Function and CLEAN Function of Excel Sheet.In this article, We would learn how to use TRIM and CLEAN Function and how they work, and also the difference between the two.

What is TRIM FUNCTION?

Image result for TRIM FUNCTION IN EXCEL

As the name suggests, TRIM Function is used to trim the extra spaces from a Text String.It removes all the extra spaces leaving only one space between two words and no space at the start or at the end of the text string.

HOW TO USE TRIM FUNCTION?

SYNTAX USED FOR TRIM FUNCTION:

=TRIM(text)

ARGUMENTS:

  • text: Text String is the only and the required parameter of the TRIM FUNCTION, where we define a text string with irregular extra spaces, that we want to trim using TRIM Function.

Let’s understand how we can implement TRIM Function for it.

Consider this SpreadSheet, Where we have defined four different types of occurrence of extra spaces,

Excel Trim Function Examples

And we would get these results, for the above entries,

CLEAN FUNCTION:

Excel CLEAN function

CLEAN Function is used to remove all the non-printable characters from the text String.It is basically designed to remove all the nonprintable characters that are represented by the numbers 0-31 of the 77-bit ASCII Code, which are generally found at the beginning or at the end of a text String, supplied from a different application into MS Excel.

HOW TO USE CLEAN FUNCTION?

SYNTAX FOR CLEAN FUNCTION:

    =CLEAN(text)

ARGUMENTS:

  • text: The required parameter and the only parameter of the CLEAN Function,”text”, specifies the text string that we want non-printable characters free.

Let us understand it through an example,

Consider this SpreadSheet, where some text Strings are specified with some nonprintable characters, and as you can see implementing CLEAN Function over it, all the nonprintable characters are removed.

Microsoft Excel

So, This is How TRIM Function and CLEAN Function Works, they both are almost same but yet a little bit different, If you have observed TRIM Function removes all the extra spaces from the text String, whereas CLEAN Function removes all the non-printable characters from the text String.

NOTE:

  • TRIM and CLEAN Function also work together to remove all the extra spaces and nonprintable characters from a text string imported from a web page or any applications.Many times while copying data, some extra data that is not meant to be the part of the useful data get imported, that we can easily remove by using TRIM and CLEAN Function together.
  • SYNTAX Used for using TRIM and CLEAN Function together is,

=TRIM(CLEAN(text))

So, This is all about TRIM and CLEAN Function.If you think this post helped you in learning Excel Functions clearly, then Bookmark this website for more useful Information and tips related to Excel Queries and share with your Colleagues and Friends who are facing problem in understanding and implementing Excel Functions.If you have any question regarding this post then you can freely post it to us, We’ll be happy to answer you.

Add a Comment

Your email address will not be published. Required fields are marked *