Excel TRIM Function (Table of Contents)
TRIM in Excel
The trim function is used to remove the extra spaces from the start and end of the cell value. This is quite useful when we have the same values in a column, but some of the cells contain extra spaces. By this Trim function, we can remove the extra spaces from any cell, irrespective of the number of spaces that are there in that selected cell.
TRIM Formula in Excel
Below is the TRIM Formula in Excel:
Trim function arguments
There is only one argument in the Trim function, which is below mention.
=Text(Cell Value / Text)
Shortcut of using the formula
Click on the cell where you want to result in value then put the formula as below mention
= Trim(Cell Value / Text)
Steps for using the Trim function in Excel
- Click on formula tab > Text > click on Trim
- Also, click on the function icon, then manually write and search the formula.
- We get a new function window showing in below mention pictures.
- Then we have to enter the details as shown in the picture.
Put the Cell value or text value where you want to remove space from data.
- Then Click On OK.
- The Result is:
How to Use the TRIM Function in Excel?
Trim Function in Excel is very simple and easy to use. Let us now see how to use the TRIM function in Excel with the help of some examples.
Suppose we have a column of customer names that have some space before and after the text and more than one space between the words. So, how do we remove all extra space in a cell at a time on overall data by copying an Excel TRIM formula across the column and then replacing formulas with their values? Let’s start to write the formula; the detailed steps are mentioned below:
- Write a TRIM formula for the first-row cell, A2, in our example:
The Result is:
- A position of the cursor to the lower right corner of the formula cell (B2 in this example),
- Select the range (B2:B12) and fill down the formula, up to the last cell with data.
- As a result, you will have 2 columns of original names with spaces and formula trimmed customers’ names.
Now, replace the values in the original column with the trimmed data value, But be careful, simply copy and paste the trimmed column data value over the original column; otherwise, destroy your formulas, and we also we lost the data. So such type of this happen, you need to copy only values, not formulas, Steps are below mention.
- Select all cells with Trim formulas (B2:B12 in this example), and copy (CTRL+C) the selected range.
- Select all cells with the original data (A2:A12)
- And Paste the data as value only, not a formula; you can also use a shortcut method for paste special, (Ctrl+Alt+V). The dialog box will appear.
- Then again, V for value.
- Press the Enter, and the result will look like
Normally when we fetch data in excel from another application or any database like SQL Server, Oracle, HTML, we face such type issue for line breaking with extra space; we can say that double line issue or wrap text issue, and also ever include with a special character which is not removed with only trim. So we can use a trim function with a clean function for such type situations. Example showing as below mention pictures.
So now we can use a trim function with a clean function.
- Write the formula =TRIM(CLEAN(A16))
The Result is :
- The cursor Position to the lower right corner of the formula cell (B16 in this example)
- Select the range and fill down the column with the formula.
The Result is:
- Then you need to copy and paste values only, not formulas like in the first example.
Explanation of the TRIM Function in Excel
Before removing spaces in data, if we want to know how much extra space or total space in the excel sheet, we get the LEN function’s help with a trim function. If you want to get the total numbers of extra spaces in a cell, then, first of all, we find the total numbers of text length using the Len function, then calculate the string length without extra spaces and subtract the latter from the total length. The formula is as showing below mention.
The following screenshot shows the above formula in action:
The Result is:
Note: This function returns the count of extra spaces in a cell, but this function will be not useful as we want to count with a single space also or spaces in the middle of text because trim removed only the unwanted spaces. If we want to get the total numbers of spaces in a cell, we can use a substitute formula with the LEN function.
As I remember above, we have used the trim function with a LEN function for extra spaces count. The same activity we are doing here for total spaces count uses the LEN function with a substitute function. Substitute function replaces old text as a new value; in below mention an example, we substrates the only text value from total length count; thus, we found a total no of space.
The Result is:
Things to Remember
- TRIM will remove extra spaces from the text. Thus, it will leave only single spaces between words and no space characters at the start or end of the text.
- The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from the text, so it only removes the ASCII space character (32) from the text.
- The TRIM function will not remove non-breaking space characters commonly used in HTML web pages.
- The Unicode text often contains a non-breaking space character (160) that appears in web pages as an HTML entity. It will not be removed with TRIM.
This has been a guide to TRIM in Excel. Here we discuss the TRIM Formula and how to use the TRIM function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –