Shakil Ahmed
Excel provides several text functions to help you perform your desired tasks easily and swiftly. Today we are going to show you how to use a text function called: TRIM. For this session, we are using Excel 2019, feel free to use yours (at least 2003).
Practice Workbook
You are welcome to download the practice workbook from the link below.
Excel TRIM Function
1. Basics of TRIM Function
The Excel TRIM function is categorized under the TEXT functions. It removes the extra spaces from a text string.
Summary
Removes all spaces from a text string except for single spaces between words.
Syntax
TRIM (text)Arguments
text: The text string from which to eradicate unnecessary spaces.
Versions
Workable from Excel 2003.
2. Uses of TRIM Function
I. Remove Extra Spaces
The description of the TRIM function might have let you understand that the prime task of this function is to remove any extra spaces from a string. Let’s explore with examples.
Our example dataset contains few movie names.
Here we have intentionally put extra spaces in the beginning, middle, or end of the names. To remove these extra spaces, we need to provide the Cell Reference of the text within TRIM.
=TRIM(B4)B4 is the Cell Reference for the first row of the Movie column.
You can see the spaces between Few and Good have been eradicated (only one space remains).
A similar formula (change in Cell Reference) will remove the spaces from the beginning, middle, and end for the rest of the rows.
II. Remove Spaces and Clean String
Sometimes merely using TRIM may not be useful to clean data. The function removes spaces, but if our dataset has a string where texts are separated into different lines?
We have brought the dataset of movie names and their respective releasing year. Here movie names and the release year are in different lines.
There are extra spaces also. To eradicate the issues and make the data an organized one, we are going to use a function called CLEAN along with TRIM.
The CLEAN function converts text to be cleaned of line breaks and other non-printable characters.
For the first row, our formula will be
=TRIM(CLEAN(B4))
The formula provided the cleaned data, no extra spaces, no line breaks.
III. Eradicate Leading Spaces Only
The TRIM function removes all the extra spaces from every part. But it can be also used for removing the leading spaces only.
To show you examples, we have introduced a dataset of several area codes.
The area codes have spaces at the beginning as well as in between the words. We aim to remove the spaces from the beginning only.
We will use MID, FIND, and LEN along with TRIM. To know more about these functions, visit these articles: MID, FIND, LEN.
And the formula will be:
=MID(B4,FIND(MID(TRIM(B4),1,1),B4),LEN(B4))
The combination of FIND, MID, and TRIM calculates the position of the first text character in a string.
And then, supply that number to the outer MID function so that it returns the entire text string starting at the position of the first text character.
IV. Concatenate with TRIM
We can combine the values from different cells using the TRIM function.
Our example dataset consists of a few random items, we are set to combine them together.
To be honest we don’t need TRIM to combine. We will only use TRIM when the combining values have extra spaces.
So, our formula will be
=TRIM(B4&" "&C4&" "&D4) & will concatenate the values. And the TRIM functions will remove all extra spaces.
TRIM automatically strips space at the start and end of a given string and leaves just one space between all words inside the string. It takes care of extra spaces caused by empty cells.
We can replace the space between two items using the SUBSTITUTE function. Visit this SUBSTITUTE article for further information.
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5)," ",", ")
SUBSTITUTE is used to replace each space (" ") with a comma and space (", ").
V. Count Spaces in a String
The TRIM function can be helpful to count the number of spaces from a string. We are using the movie dataset for showing you examples.
To find the extra spaces in the string, we are going to use the formula written below.
=LEN(B4)-LEN(TRIM(B4)) 
LEN provides the length of the string. To know more about the function visit the LEN article.
LEN(B4) provided the full length of the string of cell B4 and LEN(TRIM(B4)) provided the length after trimming.
Subtraction of these two will provide the total number of extra spaces.
VI. Count Words in a String
We can count the number of words present in a string using the TRIM function though we need to get help from other functions also.
We need to use the LEN, and SUBSTITUTE functions along with TRIM.
Our formula will be
=(LEN(TRIM(B4))-LEN(SUBSTITUTE(B4," ","")))+1
We have found the number of words from this string.
SUBSTITUTE removes all spaces from the string, then LEN calculates the length without spaces.
This number is then subtracted from the length of the text with spaces. We have used TRIM to eradicate any spaces at the beginning or end of the string.
And finally, 1 is added to the result, since the number of words is the number of spaces + 1.
The same formula will provide the number of words for the rest of the texts.
VII. Numbers TRIM
You can provide numbers within the TRIM function. To show you examples we have picked several numbers randomly.
Use the trim function as below
=TRIM(B4)
We have found the trimmed number.
But are they really numbers? Let’s check by summing them together.
Oh, dear! The sum of the value is 0.
This is because the digits you are seeing are not numbers, they are in Text format. To convert these into numbers after TRIM we will use a function called VALUE.
And the formula will be
=VALUE(TRIM(B4)) 
The first-row value became Number and you can see the sum result changed as well. For the rest of the values, we are using the AutoFill feature.
All the values are now Numbers and they can be added.
3. Quick Notes
- You can directly insert the text into the TRIM function.
You will find the trimmed text.
- The TRIM function only removes the space character from the text.
Here only spaces have been removed. The line breaks remain as it is.
Conclusion
That’s all for today. We have tried showing how you can use the TRIM function. You can use the function to remove extra spaces from a string as well as several advanced operations like words or spaces count. Hope you will find this helpful.
Feel free to comment if anything seems difficult to understand. Let us know any of your TRIM function-related scenarios where you have stuck, we are ready to help.
