Use TRIM Function in Excel to Remove Extra Spaces from Words Numbers Non printable Characters within a cell

In this article we shall discuss about the TRIM function in Microsoft Excel, sometimes working with data you might be noticed that some formula does not work although everything is right or proper . This is because some formula will not work in excel if there is extra space before a cell's data or after a cell's data (Leading or Trailing space) while trying to match columns or cells. Space(s) in excel is also a character which is not ignored by formulas. The problem with spaces are they are invisible to human eye, you need to take special care to detect them.

In this article we shall learn how to use TRIM function with some function combined or nested and will resolve or remove possible reasons of formula not working in Excel.

Description

Excel TRIM function returns a text value with the leading and trailing spaces removed. You can also use the TRIM function to remove unnecessary spaces between words in a string.

The TRIM function is a built-in function in Excel that is categorized as a String/Text Function.

Syntax

The syntax for the TRIM function in Microsoft Excel is:

=TRIM( text )

Parameters or Arguments

text

The text value to remove the leading and trailing spaces from.

Before going to several examples let us first look at nature of the problem with spaces.

Space Before or After a Data Creates Wrong Results

As you can see from the above image although both the texts are same in C and in D cell but when I ask excel to check whether these texts are equal or not, it replies me 'No' or 'False'. Human eye is showing these texts are same because we couldn't able to see the space before this text. Removing this extra space will resolve this problem. Or we can use TRIM function to remove the space.

Examples

Remove Spaces in Excel - Leading, Trailing, in between words

One more thing I would like to add here is that you can use LEN function to check the length of the text or contents in the cell and then you can count manually number of characters in that cell just to check it. 
Let me show you the working of TRIM function by the help of the below image.


To use TRIM function you need to type an '=' sign and then TRIM, press tab and move your cursor to the cell to trim. In the below picture I am showing you how to trim a word, spaces within words or spacing between words, spaces before numbers, spaces after numbers and both.

Use of TRIM Function in Excel

Please note the difference in number of of characters in the B column and in the E column, before and after use of TRIM function. It is very easy function but it can save your time of troubleshooting a problem in formula. Remember a trailing or leading spaces in any cell will make your formula inconsistent.

Remove Non printable characters

When you import data from external sources, it's not only extra spaces that come along, but also various non-printing characters like carriage return, line feed, vertical or horizontal tab, etc.

The TRIM function perfectly removes white spaces, but it cannot eliminate non-printing characters. Technically, Excel TRIM is designed to only delete value 32 in the 7-bit ASCII system, which is the space character.

To remove spaces and non-printing characters in Excel, use TRIM in combination with the CLEAN function. As its names suggests, CLEAN is purposed for cleaning data, and it can delete any and all of the first 32 non-printing characters in the in the 7-bit ASCII set (values 0 through 31) including line break (value 10).

Syntax of CLEAN

=CLEAN(text)

It removes all the non printable characters in a cell. Let us have an example. In the below example I have typed few non printable characters. This type of or other non printable characters you may get while importing data from other sources. In this situation what I have done is that I used CLEAN function to remove these non printable characters and cover it by TRIM function to remove the extra trailing of leading spaces. Please follow the below image to understand this small but useful function.

How to use of TRIM and CLEAN Function in Excel

Please note that yellow colored non printable characters, remove it by CLEAN and TRIM function. For the above example the formula is =TRIM(CLEAN(B3)).

I hope you liked this article, thank you for reading....