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.

**Table of Contents**Expand

## 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
as the*B5***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
cell to the*C5*cell.*C9*

- 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
cell as an organized form.*C5* - Therefore, use the
**Fill Handle**tool and drag it down from thecell to the*C5*cell.*C9*

- 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**.

**Formula Breakdown**

- 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 thecell to the*C5*cell.*C9*

- 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
cell.*E5* - Secondly, write down the following formula.

`=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5)," ",", ")`

- Then, press
**Enter**.

**Formula Breakdown**

**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 thecell to the*E5*cell.*E9*

- 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
cell here.*C5* - 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**.

**Formula Breakdown**

**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 celland*B5***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 thecell to the*C5*cell.*C9*

- 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
cell.*C5* - Then, write down the following formula.

`=(LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ","")))+1`

- After that, press
**Enter**.

**Formula Breakdown**

**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
cell.*C5* - 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 thecell to the*C5*cell.*C9*

- Finally, we have found the trimmed number.
- But are they really numbers? Let’s check by summing them together.
- So, we will choose the
cell to apply the below formula.*C11*

`=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**