How to use TRIM function in Excel (7 Examples)

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

Overview - Excel TRIM Function

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.

Syntax - Excel TRIM Function

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.

Basic use dataset - Excel TRIM Function

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.

Formula TRIM -example 1 - Excel TRIM Function

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.

AutoFill TRIM basic use - Excel TRIM Function

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.

Remove space & Clean dataset - Excel TRIM Function

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))

Remove space & Clean formula result - Excel TRIM Function

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

Remove space & Clean autofill - Excel TRIM Function

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.

Eradicate Leading Spaces example dataset - Excel TRIM Function

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))

Eradicate Leading Spaces example result - Excel TRIM Function

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.

Eradicate Leading Spaces example AutoFill - Excel TRIM Function

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.

Combine With TRIM - Excel TRIM Function

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)," ",", ")

Combine With TRIM formula result - Excel TRIM Function

SUBSTITUTE is used to replace each space (" ") with a comma and space (", ").

Combine With TRIM AutoFill - Excel TRIM Function

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.

Count Spaces in String - Excel TRIM Function

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

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

Count Spaces in String formula result- Excel TRIM Function

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.

Count Spaces in String formula result autofill- Excel TRIM Function

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.

Count Words in String - Excel TRIM Function

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

Our formula will be

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

Count words in result formula - Excel TRIM Function
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.

Count Words in String formula result autofill- Excel TRIM Function

VII. Numbers TRIM

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

Numbers TRIM - Excel TRIM Function

Use the trim function as below

=TRIM(B4)

Numbers TRIM formula- Excel TRIM Function

We have found the trimmed number.

Numbers TRIM formula autofill - Excel TRIM Function

But are they really numbers? Let’s check by summing them together.

Sum numbers - Excel TRIM Function

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

SUM - Excel TRIM Function

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)) 

VALUE - TRIM - Excel TRIM Function

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.

Autofill numbers - sum - Excel TRIM Function

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

3.Quick Notes

  1. You can directly insert the text into the TRIM function.

Direct input - Excel TRIM Function

You will find the trimmed text.

Direct input result - Excel TRIM Function

  1. The TRIM function only removes the space character from the text.

Line breaks - Excel TRIM Function

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

Result - Excel TRIM Function

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.


Further Readings:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo