Excel Trim Formula (Table of Contents)
Introduction to TRIM Formula in Excel
Trim function comes under the list of string/text functions in excel. This built-in function helps us to remove unwanted spaces within the data. The trim function returns data after removing unnecessary spaces. This can be prior to a word or space following a text. This function helps to clean your data which causes errors and difficult to detect.
Syntax of TRIM Formula
Argument of TRIM Formula:
The text refers to a cell or text.
How to Use TRIM Formula in Excel?
The trim function helps us to remove the unwanted spaces between words in a string, numbers, etc. this unwanted spaces may cause to produce wrong results when you apply a formula in it. The spaces may occur tailing to the data or prior to the data. This will affect data manipulation if it is numbers. And for texts, it may occur in between the words too. Let’s understand how to use the TRIM Formula in Excel with some examples.
Example #1 – Find the Total of Purchased Amount
Let’s see how the extra spaces within the data make problems for you. Below is the list of items purchased. We want to find the total of the purchased amount.
Select the immediate next cell where the amount ends.
And apply the formula to find the sum of amounts for 8 items.
After using formula the answer is shown below.
The formula applied is =SUM (B2: B9), the entire item amount is added. But the sum of the amount is wrong, some amounts are not added. While checking it is found the first item’s amount is not added. By examining again, we found an unnecessary space trailing with the first amount.
Until this extra space gets removed it won’t produce the correct amount while applying the formula. To remove this space, apply the trim function as below. This will clear the data from unwanted spaces.
4.8 (5,522 ratings)
View Course
By selecting cell C2 apply the formula as =TRIM(B2) and press Enter. Where C2 is the cell which contains the data with extra space with it.
Copy and paste as values in cell C2 after the extra space is removed from the data. Now the unwanted space is removed and the sum of amounts is changed as below.
Here the total was 1032 when the formula is applied initially, but after removing the unnecessary spaces it gave the correct result for the applied formula where the amount for 8 items is included.
Example #2 – Using COUNT & TRIM Function
Given is the list of loan issued by a bank and the details include Loan_ID, Gender, Marital status, etc. We want to get the count of loan issued.
Let’s try to apply the COUNT function to get the number of loans issued to the Loan_ID column.
Select the cell B8 and apply the formula =COUNT (A2:A8), then press Enter. Where A2:A8 contains the Loan_ID for each loan.
The result is shown as ‘0’ instead of 6. If you check the data given an unwanted space can see before the Loan_ID.
By using the trim formula this unnecessary space can be removed. Since this space exists the count function will never produce proper results. So select cell F2 and apply the trim formula for Loan_ID.
After removing the unwanted spaces, you can see the applied formula is worked to find the count of loan. The number of loans granted is 6.
Example #3 – Remove the Unwanted Spaces in String or Characters
Let’s see how the trim function can be used to remove the unnecessary spaces between the string data. Some survey questions are listed in an excel workbook and the options are also mentioned.
You can see the data is scattered and unable to read it properly. It exits more spaces between the words and difficult to understand. By applying the trim function see how the data can be aligned properly. Select C3 and apply the formula =Trim(B3)
Press Enter key and see how the unwanted spaces are replaced.
Apply the TRIM formula to the entire cell and the data will look as below.
The scattered data is aligned properly and now the options for the given question are easy to read.
Things to Remember
- The trim function deletes spaces between words except for a single space.
- If the strings contain unwanted spaces, it will be difficult to understand.
- Unwanted spaces in the data will stop you from producing proper results while applying the formula.
- For integers or number type data, the space prior to or tailing will affect on the data manufacturing.
- The trim function will leave single space between words in a string and remove extra spaces apart from this.
- Trim clears ASCII character (32) from a text.
Recommended Articles
This is a guide to TRIM Formula in Excel. Here we discuss How to use TRIM Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
