TRIM Function in Excel

Trim function in excel as the name suggests itself it trims some part of any string, why string because it is a text function, the function of this formula is that it removes any space in a given string but it does not remove if there is a single space is present between two words but any other unwanted spaces are removed.

TRIM Formula in Excel

Explanation of Trim Formula in Excel

The trim formula in Excel has only one compulsory parameter i.e. text.

Compulsory Parameter:

text: it is a text from which you want to remove extra spaces.

How to Use TRIM Function in Excel?

TRIM function in excel is very simple and easy to use. Let understand the working of TRIM function in Excel by some examples. Excel Trim function can be used as a worksheet function and as a VBA function.

You can download this TRIM Function Excel Template here – TRIM Function Excel Template

Example #1

In this example, trim function in excel is used to remove the extra spaces from the give texts/ words from the beginning and at the end.

And the result will be in the second column. Applying trim on “Tanuj” the output will be “Tanuj” =TRIM(B5).

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Example #2

In this example, the trim removes the extra spaces from the middle of on the given text string as shown in the below table.

For “Note that the Trim function is similar to” =TRIM(B19), the output will be “Note that the Trim function is similar”

Example #3

Suppose you need to calculate the number of words in the string –“Note that the Trim function is similar” you can use =LEN(B31)-LEN(SUBSTITUTE(B31,” ”,””))+1. It will count the number of spaces and add one to it.

But if there is extra spaces between the string then will not work so we use trim to achieve it. Here we use the =LEN(TRIM(B32))-LEN(SUBSTITUTE(B32,” “,””))+1 function this will first remove extra spaces then count the number of spaces and add one to give the total number of words in the string.

Example #4

To join the multiple columns with separated with comma we can use the trim function and substitute function in excel.

Trim will be =SUBSTITUTE(TRIM(F5&” “&G5&” “&H5&” “&I5),” “,”, “).

Things to Remember

TRIM function removes extra spaces from the text and leaves no extra space b/w words and no beginning and end space at the string.

TRIM Function is only able to remove the ASCII space character (32) from text/string.
Trim in excel is not able to remove the Unicode text often contains a non-breaking space character (160) that appears in web pages as the HTML entity.
TRIM in excel is very useful to clean the text from other applications or environments.