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**. For this session, we are using Excel 2019, feel free to use yours (at least 2003).

**Table of Contents**hide

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## TRIM Function

### 1. Basics of TRIM

The Excel **TRIM **function is categorized under the **TEXT **functions. It removes the extra spaces from a text string.

#### Summary

Removes all spaces from a text string except for single spaces between words.

#### Syntax

`TRIM (text)`

#### Arguments

**text: **The text string from which to eradicate unnecessary spaces.

#### Versions

Workable from Excel 2003.

### 2. Uses of TRIM

#### I. Remove Extra Spaces

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.

Our example dataset contains few movie names.

Here 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 within **TRIM**.

`=TRIM(B4)`

**B4 **is the **Cell Reference **for the first row of the *Movie *column.

You can see the spaces between *Few *and *Good *have been eradicated (only one space remains).

A similar formula (change in **Cell Reference)** will remove the spaces from the beginning, middle, and end for the rest of the rows.

#### II. Remove Spaces and Clean String

Sometimes merely using **TRIM **may not be useful to clean data. The function removes spaces, but if our dataset has a string where texts are separated into different lines?

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 an organized one, we are going to use a function called **CLEAN **along with **TRIM.Â **

The **CLEAN** function converts text to be *cleaned* of line breaks and other non-printable characters.

For the first row, our formula will be

`=TRIM(CLEAN(B4))`

The formula provided the cleaned data, no extra spaces, no line breaks.

#### III. Eradicate Leading Spaces Only

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.

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 **along with **TRIM. **To know more about these functions, visit these articles: MID, FIND, LEN.**Â **

And the formula will be:

`=MID(B4,FIND(MID(TRIM(B4),1,1),B4),LEN(B4))`

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.

#### IV. Concatenate with TRIM

We can combine the values from different cells using the **TRIM **function.

Our example dataset consists of a few random items, we are set to combine them together.

To be honest we donâ€™t need **TRIM **to combine. We will only use **TRIM **when the combining values have extra spaces.

So, our formula will be

`=TRIM(B4&" "&C4&" "&D4)Â `

* & *will concatenate the values. And the

**TRIM**functions will remove all extra spaces.

**TRIM** automatically strips space at the start and end of a given string and leaves just one space between all words inside the string. It takes care of extra spaces caused by empty cells.

We can replace the space between two items using the **SUBSTITUTE **function. Visit this SUBSTITUTE article for further information.

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

**SUBSTITUTE** is used to replace each space `(" ")`

with a comma and space `(", ")`

.

#### V. Count Spaces in a String

The **TRIM **function can be helpful to count the number of spaces from a string. We are using the movie dataset for showing you examples.

To find the extra spaces in the string, we are going to use the formula written below.

`=LEN(B4)-LEN(TRIM(B4))`

`Â `

**LEN **provides the length of the string. To know more about the function visit the LEN article.

**LEN(B4) **provided the full length of the string of cell **B4 **and **LEN(TRIM(B4)) **provided the length after trimming.

Subtraction of these two will provide the total number of extra spaces.

#### VI. Count Words in a String

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.

We need to use the **LEN**, and **SUBSTITUTE** functions along with **TRIM**.

Our formula will be

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

We have found the number of words from this string.

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

The same formula will provide the number of words for the rest of the texts.

#### VII. Numbers TRIM

You can provide numbers within the **TRIM **function. To show you examples we have picked several numbers randomly.

Use the trim function as below

`=TRIM(B4)`

We have found the trimmed number.

But are they really numbers? Letâ€™s check by summing them together.

Oh, dear! The sum of the value is 0.

This is because the digits you are seeing are not numbers, they are in *Text *format. To convert these into numbers after **TRIM **we will use a function called **VALUE.Â **

And the formula will be

`=VALUE(TRIM(B4))Â `

The first-row value became *Number *and you can see the sum result changed as well. For the rest of the values, we are using the **AutoFill **feature.

All the values are now *Numbers* and they can be added.

### 3.Quick Notes

- You can directly insert the text into the
**TRIM**function.

You will find the trimmed text.

- The
**TRIM**function only removes the space character from the text.

Here only spaces have been removed. The line breaks remain as it is.

## Conclusion

Thatâ€™s all for today. We have tried showing how you can use the **TRIM** function. 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.