Excel TRIM Function – How To Use

The TRIM function removes all the leading and trailing spaces from a string. In addition to this, it also removes extra spaces between the words within a text, leaving a single space between words. It is mainly used to fix improper spacing in the text.

The TRIM function is very helpful when concatenating different sets of data from several cells into one. It helps in striping extra spaces from text and makes the data even more presentable.

Excel TRIM Function

Syntax

The syntax of the TRIM function is as follows:

=TRIM(text)

Arguments:

'text' – argument can be a string, text, or number inserted directly using double quotes or a cell reference containing the data. The text argument can also be another function that returns text which needs to be trimmed for extra spaces.

Important Characteristics of the TRIM Function

The most notable feature of the TRIM function is that it removes the additional space character (ASCII value 32) from the text. Other noteworthy characteristics of the TRIM function are as follows:

  • The TRIM function does not remove the non-breaking space character (' ' ASCII value 160).
  • It is a text function, so even if the text argument is numeric, the return value will always be in text format.
  • When stripping the text for extra spaces, the TRIM function leaves one space between words and removes all spaces at the start or end of the string.

Examples

To begin with, let's check the basic functionality of the TRIM function. Then in the subsequent examples, we will explore different objectives that can be achieved using the TRIM function.

Example 1 – Remove Additional Space from Data

In this example, we have a dataset containing Marvel movie names that came as a recommendation from a friend. Upon downloading, inconsistent extra spaces exist in the names, which doesn't look very appealing.

Remove Additional Space from Data

We will simply use the TRIM function to clean all the extra space, as shown below.

=TRIM(B3)

Remove Additional Space from Data

The first movie name in cell B3 contains extra space in the beginning, which is removed by the TRIM function. The second movie name in cell B4 includes extra space in the beginning and also in between. The TRIM function removes both leaving one space between words.

In the last example, it considers 'Tho' and 'r:' as separate words and leaves a space in between.

To get rid of all the spaces, we can use the SUBSTITUTE function to replace the space character with an empty string.

Now, we have seen that the TRIM function cleans all the extra spaces, such as the space, in the beginning, trailing spaces, and extra spaces between words leaving a single space. But what if we want to retain the space between the words for clarity?

Let's find out in the next example how to use the TRIM function only to remove the leading space.

Example 2 – Only Clean Leading Spaces in Text

In this example, we have a data set containing the name and addresses of people. It contains unnecessary space in the beginning, which must be removed. However, let's say for the sake of clarity, we want to keep the double spaces between street name, city, state, and pin code as it makes it easier to read.

Only Clean Leading Spaces in Text

To alter the functionality of the TRIM function and use it as per our needs, we will combine the TRIM function with other Excel functions such as the MID, LEN, and FIND functions.

We will use the MID Function to determine the first letter of the string. Then we will find the location of the first letter using the FIND function. We can then extract the string using the MID function starting from the position of the first letter till the total length of the string. That way, we will be able to get rid of the space in the beginning and still keep the spaces in between.

Let's put the above-stated logic in the form of a formula.

Firstly, we will use the MID and TRIM functions to extract the first letter of the string.

=MID(TRIM(C3),1,1)

In this case, the first character in cell C3 is a space character, however, when we extract the first character of the string after the TRIM function, it will be '2' Moving on, we will find the location of the first letter (in this case, number 2) in the original string. The formula used will be as follows.

=FIND(MID(TRIM(C3),1,1),C3)

Only Clean Leading Spaces in Text

The return value would be 17. We will now use the MID function to extract the string starting from position 17 up to the total length of the string. This will give us the final formula.

=MID(C3,FIND(MID(TRIM(C3),1,1),C3),LEN(C3))

Only Clean Leading Spaces in Text

We finally have the required address where we have cleaned the leading space but could retain the middle spaces.

Example 3 – Count the Number of Words in a String

In this example, we have to shortlist quotes that we can publish on our social media platforms, but we have an instruction that there cannot be more than 15 words. We downloaded a set of famous quotes from the internet.

Count the Number of Words in a String

Counting the number of words manually would be too time-consuming and not an efficient way to filter the dataset. Instead, we can use the TRIM function combined with the SUBSTITUTE and LEN functions to our advantage.

The usual observation is that the number of words in a sentence is the number of spaces plus 1. For e.g., In the string "Practice makes perfect", there are two spaces; therefore, the number of words is (2+1) 3. Using the same logic, we can formulate our formula.

The formula counts the number of characters in each string or cell, both with and without spaces, using the LEN function. It then utilizes the difference between the length to calculate the word count. This works as long as there is a space between each word since the word count equals the number of spaces plus 1.

Let's compile the formula. As we want to eliminate any extra spaces because that will skew our word count, we will use the TRIM function and then calculate the length. The function will be as follows.

=LEN(TRIM(B3))

Now, we will find the length of the string without any spaces. The formula used will be as follows.

=LEN(SUBSTITUTE(B3,"",""))

We will replace all the space in the original string with an empty string using the SUBSTITUTE function, then find the length of the string. The difference between the two will give us the number of spaces.

=LEN(TRIM(B3))-LEN(SUBSTITUTE(B3," ",""))

In this case, cell B3 contains 7 spaces. If we add 1 to it, we will have our number of words.

=LEN(TRIM(B3))-LEN(SUBSTITUTE(B3,"",""))+1

Count the Number of Words in a String

Now we can easily filter quotes containing 15 words or less. But if you observe, the last cell, C14, is empty, yet the number of words is 1. That is because it adds 1 in all conditions. Let's refine our formula more to avoid such scenarios.

We can check if there is a text in the cell. If yes, add 1 else, add 0. We can replace "1" with the following formula:

=LEN(TRIM(B3))>0

This formula determines the length after using the TRIM function. If the cell contains text, LEN will return a value greater than 0, therefore TRUE. If the cell is empty or only contains space, the LEN function will return 0, hence FALSE.

When used in a mathematical operation in Excel, TRUE & FALSE return 1 and 0, respectively. We will use that to our advantage. Otherwise, the IF function statement can also be used to express this logic in the following way:

=IF(LEN(TRIM(B5))>0,1,0)

We will use the more refined formula to count the number of words in a string.

=LEN(TRIM(B3))-LEN(SUBSTITUTE(B3,"",""))+(LEN(TRIM(B3))>0)

Count the Number of Words in a String

We can now add the quotes which contain 15 words or less. Try to use the logic for other applications on your own.

Example 4 – Clean Spaces when TRIM Function is Not Working

We downloaded a few online messages, but they contain extra spaces. To use them further, we have to remove the unnecessary spaces.

Clean Spaces when TRIM Function is Not Working

As the TRIM function removes the extra spaces, let's try to use it to clean our data.

Clean Spaces when TRIM Function is Not Working

As we can see, few spaces were removed while few remains. TRIM function helps us to remove all unnecessary spaces in the dataset. If the TRIM function cannot remove the space in data, the most probable reason is that the data includes non-breaking space.

The non-breaking space character ( ) is often used in HTML web pages. The TRIM function removes the space character corresponding to ASCII value 32 or CHAR(32), while the non-breaking space character is CHAR(160). So, let's use the SUBSTITUTE and TRIM functions to clean the data.

=TRIM(SUBSTITUTE(B3,CHAR(160)," "))

The SUBSTITUTE function will replace all non-breaking space characters with a space character, which means substituting CHAR(160) with CHAR(32). Then the TRIM function removes all unnecessary spaces, leaving the data presentable.

Clean Spaces when TRIM Function is Not Working

We can also go one step further and add the CLEAN function if the extra space is not a non-breaking space character but a non-printable character. The CLEAN function removes all non-printable characters from the data.

The final formula would be like:

=TRIM(CLEAN(SUBSTITUTE(B3, CHAR(160), " ")))

TRIM vs CLEAN Function

Both TRIM and CLEAN functions are text functions in Excel used for cleaning the data. The TRIM function removes extra spaces, which is ASCII value 32 (CHAR(32)), while the CLEAN function removes the non-printable characters from the data. The non-printable characters map to the first 32 characters of the ASCII code (0-31).

So, if you wish to remove any character from 0-32, use the CLEAN function and for ASCII character 32, use the TRIM function.

With this, we have discussed most things related to the TRIM function in Excel. Practice and discover new applications of the function. While you master this function completely, we'll have another Excel tutorial ready for you to get your hands dirty with.

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.