Updated August 16, 2023
Introduction to TRIM Formula in Excel
The TRIM Formula in Excel 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 before a word or space following a text. This function helps to clean your data which causes errors and is difficult to detect.
Syntax of TRIM Formula
How to Use TRIM Formula in Excel? The argument of the TRIM Formula:
The text refers to a cell or text.
The trim function helps us remove the unwanted spaces between words in a string, numbers, etc. When you apply a formula, these spaces may cause you to produce the wrong results. The spaces may occur tailing to the data or before 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 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 the formula, the answer is shown below.
The formula applied is =SUM (B2: B9), and the entire item amount is added. But the sum of the amount is wrong; some amounts are not added. While checking, the first item’s amount is not added. We found an unnecessary space trailing with the first amount by examining again.
The formula won’t produce the correct amount until you remove this extra space. To remove this space, apply the trim function as below. This will clear the data from unwanted spaces.
By selecting cell C2, apply the formula as =TRIM(B2), and press Enter, where C2 is the cell that contains the data with extra space with it.
Copy and paste as values in cell C2 after removing the extra space from the data. The unwanted space is removed, and the amounts are changed as below.
The total was 1032 when the formula was applied initially. Still, removing the unnecessary spaces gave the correct result for the applied formula, where the amount for 8 items is included.
Example #2 – Using COUNT & TRIM Function
A bank gives the loan list, and the details include Loan_ID, Gender, Marital status, etc. We want to get the count of loans issued.
Let’s try to apply the COUNT function to get the number of loans issued to the Loan_ID column.
Select 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.
Once you remove the extra space, you will be able to see that the applied formula has successfully calculated the count of loans. 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 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 is 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 the Enter key and see how the unwanted spaces are replaced.
Apply the TRIM formula to the entire cell; 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.
- The space before or tailing will affect the data manufacturing for integers or number-type data.
- The trim function will leave a 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
