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 in Excel. Here is our overview image of using the TRIM function.
Introduction to Excel TRIM Function
The Excel TRIM function is categorized under the TEXT functions. It removes the extra spaces from a text string.
Objectives
Remove all spaces from a text string except for single spaces between words.
Syntax
=TRIM (text)Arguments Explanation
| Argument | Required/Optional | Explanation |
|---|---|---|
| text | Required | The text string from which to eradicate unnecessary spaces |
Version
- The DVAR function is available from Microsoft Excel 2007.
- Here, we will use Microsoft Excel 365.
How to Use TRIM Function in Excel: 7 Suitable Examples
This article will demonstrate to you how to use the TRIM function in Excel. Today I will be showing how you can use the TRIM function with different functions to remove spaces, merge the TRIM and SUBSTITUTE functions for removing spaces with concatenation, combining LEN and TRIM functions for counting and highlighting extra spaces, incorporating the TRIM function with LEN and SUBSTITUTE functions for counting words, Combining the TRIM and VALUE functions to delete spaces for numerical values.
Example 1: Using TRIM Function to Remove Extra Spaces from Left/Right
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.
Steps:
- So, our example dataset contains a few movie names.
- Firstly, 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 by applying the following formula.
=TRIM(B5)- Then, select B5 as the Cell Reference for the first row of the Movie
- After that, press Enter.
- Consequently, you can see the spaces between Few and Good have been eradicated (only one space remains).
- Therefore, use the Fill Handle tool and drag it down from the C5 cell to the C9 cell.
- Finally, a similar formula (change in Cell Reference) will remove the spaces from the beginning, middle, and end for the rest of the rows.
Example 2: Combining TRIM and CLEAN Functions to Remove Spaces and Clean String
Sometimes merely using TRIM may not be helpful to clean data. The function removes spaces, but if our dataset has a string where texts are separated into different lines?
Steps:
- Here, 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 organized, we are going to use a function called CLEAN along with the TRIM function.
- The CLEAN function converts text to be cleaned of line breaks and other non-printable characters.
- So, for the first row, our formula will be
=TRIM(CLEAN(B5))- Then, hit Enter.
- Consequently, you can see the spaces between the texts in the C5 cell as an organized form.
- Therefore, use the Fill Handle tool and drag it down from the C5 cell to the C9 cell.
- Lastly, the formula provided the cleaned data, no extra spaces, and no line breaks.
Read More: How to Trim Spaces in ExcelÂ
Example 3: Nesting Multiple Functions with TRIM to Eliminate Leading Spaces
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.
Steps:
- 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 functions. To know more about these functions, visit these articles: MID, FIND, LEN.
- And the formula will be:
=MID(B5,FIND(MID(TRIM(B5),1,1),B5),LEN(B5))- Then, hit Enter.
- 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.
- Consequently, you can see that there are no leading spaces available in the given image.
- Therefore, use the Fill Handle tool and drag it down from the C5 cell to the C9 cell.
- As a result, you will get the final output here in the below image.
Read More: How to Trim Text in Excel
Example 4: Merging TRIM and SUBSTITUTE Functions for Removing Spaces with Concatenation
We can combine the values from different cells using the TRIM along with the SUBSTITUTE function.
Our example dataset consists of a few random items, we are set to combine them together.
Steps:
- Firstly, choose the E5 cell.
- Secondly, write down the following formula.
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5)," ",", ")- Then, press Enter.
- TRIM effectively removes all white space from the beginning and end of a string while leaving only one space between each word. It handles additional space brought on by blank spaces.
The SUBSTITUTE can be used to replace the space between two elements. So, each space (” “) is changed to a comma and space using the SUBSTITUTE command (“, “).
- After that, you will see here that this formula can concatenate the values in the below image.
- Therefore, use the Fill Handle tool and drag it down from the E5 cell to the E9 cell.
- Lastly, you will find all the texts have been concatenated with the removal of extra spaces.
Example 5: Combining LEN and TRIM Functions for Counting and Highlighting Extra Spaces
The TRIM function can be helpful to count the number of spaces in a string. We are using the movie dataset for showing you examples.
Steps:
- Firstly, select the C5 cell here.
- Secondly, to find the extra spaces in the string, we are going to use the formula written below.
=LEN(B5)-LEN(TRIM(B5))Â - Then, press Enter.
- LEN function provides the length of the string. To know more about the function visit the LEN.
- LEN(B5) provided the full length of the string of cell B5 and LEN(TRIM(B5)) provided the length after trimming. And the subtraction of these two will provide the total number of extra spaces.
- After that, you will see here that this formula can count the spaces here in the below image.
- Therefore, use the Fill Handle tool and drag it down from the C5 cell to the C9 cell.
- Finally, you will get all the results.
Example 6: Incorporating TRIM Function with LEN and SUBSTITUTE Functions for Counting Words
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.
Steps:
- So, we need to use the LEN, and SUBSTITUTE functions along with TRIM.
- Firstly, choose the C5 cell.
- Then, write down the following formula.
=(LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ","")))+1- After that, press Enter.
- The SUBSTITUTE function 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.Â
- Therefore, you will find the number of words from this string.
- So, the same formula will provide the number of words for the rest of the texts by using the Fill Handle tool.
- As a result, we have found the number of words from this string for all the cells.
Example 7: Combining TRIM and VALUE Functions to Delete Spaces for Numerical Values
You can provide numbers within the TRIM function. To show you examples we have picked several numbers randomly.
Steps:
- Firstly, choose the C5 cell.
- Then, apply the trim function below
=TRIM(B5)- Therefore press Enter.
- After that, you can see the first trimmed number here.
- Then, use the Fill Handle tool and drag it down from the C5 cell to the C9 cell.
- Finally, we have found the trimmed number.
- But are they really numbers? Let’s check by summing them together.
- So, we will choose the C11 cell to apply the below formula.
=SUM(C5:C9)- Then, press Enter.
- Oh, dear! The sum of the value is 0.
- This is because the digits you are seeing are not numbers, they are in the Text format.
- To convert these into numbers after TRIM we will use a function called
- And the formula will be
=VALUE(TRIM(B5))Â - Then, press Enter.
- The first-row value became So, for the rest of the values, we are using the AutoFill feature.
- Therefore, you can see all the output in the number format
- All the values are now Numbers and they can be added.
- Lastly, this given image shows the sum of all the numbers.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
That’s all for today. We have tried showing you how you can use the TRIM function by using 7 suitable examples. 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.
Excel TRIM Function: Knowledge Hub
- How to Trim Part of Text in Excel
- How to Trim Right Characters and Spaces in Excel
- How to Use Left Trim Function in Excel
- [Fix] TRIM Function Not Working in Excel
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
