In a given large set of data we might have some data which may have unwanted spaces between them apart from the single space between two words, it will be a very tedious task to remove all those spaces manually but excel has given us a trim function which removes all the spaces from a given string leaving the single space between texts.
Trim Formula in Excel (Table of Contents)
Excel Trim Formula
TRIM() is a built-in function in excel and is used to remove trailing, leading and excess intermittent spaces except for single spaces between words from the data and will clean up the cells. TRIM Function is very essential when we work on different MIS(Management Information System) reports.
TRIM() Formula will also ensure that whether data is clean enough us to apply formulas like VLOOKUP, SUMIFs, INDEX and so on. This is a very useful function in removing irregular spacing when we import data from different applications. TRIM Formula is used in all fields of study like financial analysis, sales reports, employee reports, etc.
Formula of TRIM(): –
Where, text – The text for which we need to remove extra spaces.
How to Use TRIM Formula in Excel?
Example #1 – Using TRIM()
Let’s assume that we have got the names of few people and companies which was extracted some application as below:
We can observe from the above table that data in the cells are not clean. We got a lot of spaces in between names and before starting of the strings. Here we are going to use TRIM Formula to clean the data by removing the extra spaces except for single spaces between words.
The result would be as follows:
Drag the Formula to other cells.
Now we can observe that additional spaces between words and spaces in the starting of the names have been removed and data is cleaned up.
Example #2 – Using TRIM() along with PROPER()
In the previous example, we can also observe that data is not in proper format like in cells no – 4 i.e. Wells Fargo the starting letter should be a capital letter “W” but it is not in proper in our data.
When we are performing TRIM() to clean the data to remove we should also make sure that data is in a proper format. So we are going to use PROPER() along with TRIM Formula to make it clean and also the proper format as follows:
The result would be:
Here we can observe the change in data i.e removal of extra spaces and also in proper format like using capital letters wherever it is required after using PROPER() and TRIM(). This combination is always helpful in making the data looks clean and proper when we present it.
Example #3 – Using TRIM() along with VALUE()
Let’s assume that we had extracted sale value of a product from the company’s ERP but we got the values in number format as below:
It is not possible and will give the error when we try to perform different statistical operations with the data of values in text format and also contains a lot of spaces inside the cell as above. So we have to use TRIM() and VALUE() together where TRIM() is to remove spaces and VALUE() is to change the values from text format to number format.
The result after using the TRIM() and VALUE() will be as below:
Drag the Formula to other cells.
In the above result, we can observe the values has been changed from a text format and also additional spaces are removed. This kind of operation will always be useful when we work on values for sales, finance related, etc.
Example #4 – Using TRIM() along with LEN()
Assume that we are working in a bank and we got the list of account holder names and we have to reconcile the data to see whether account holder names are matching with the length of actual names given in the account form (where count of letters will be done as per the boxes available to fill the name in the sheet) as per bank.
When we get this kind of data we might face with spaces included which gives a wrong picture when we calculate the length of the names for reconciliation. Assume that data is as follow:
Assume that the data here is downloaded from bank ERP and got the names with some spaces and when we find the length of the names then it will also count the spaces that are associated in the cell and the result would be as follows:
If we can observe the length of the actual letter of the name is not matching with the result as these cells contain additional unwanted spaces. In order to clean and calculate the length of such type of data we use LEN() and TRIM() together as follows:
The result of using LEN() with TRIM() Formula is given below:
Drag the Formula from C2 to C5.
Now we can see the difference in the length of the names using only LEN() and using both LEN() & TRIM() together.
Example #5 – Using TRIM() along with SUBSTITUTE()
SUBSTITUTE() is used along with TRIM() to join multiple cell values with comma.
Let’s assume that we have the data in different cells and we need to combine all the cells data in one cell. The data would look as below:
The data given in the table are the things that are available in different boxes. Now in order to list of things that are available in each individual boxes we can use SUBSTITUTE() and TRIM() together. There are many blank cells and TRIM() will help us to avoid those.
As when we take the above operation using SUBSTITUTE() and TRIM() then we can see the list of the things in one cell without any empty spaces though we selected the cells that are empty. The result would be as follows:
We can observe the change in data with cleaned up and also formed as a list by separating with comma. This kind of operation will always when we want to keep a note of things that are available in the form of the list. If you remove any product that is not available for sale or use then we will remove the product from the cell and list will get updated in the cell where we had given by showing only available items.
Things to Remember About TRIM Formula in Excel
- TRIM Function in Excel will make sure that no extra spaces in the text and also no extra space at the start and end of the text but leaving only single spaces.
- TRIM() is useful to clean up the data that is downloaded from different environment or applications and this will only remove the ASCII space character(32)
- TRIM Formula in Excel is categorized as a String/Text Function.
- Excel TRIM Formula cannot remove the non-breaking space characters that appear in web pages as the HTML entity.
This has been a guide to Trim Formula in Excel. Here we discuss how to Use Trim formula in Excel with examples and downloadable excel template. You may learn more about excel from the following articles –