Trim in Excel

Trim Function in Excel (Table of Contents)

• Trim in Excel
• TRIM Formula in Excel
• How to use TRIM function in excel?

Trim in Excel

Trim function is categories as text function in excel used to remove leading and trailing space from a string. It can be used to remove unnecessary space between words in a string. In short, the Excel TRIM function remove extra spaces from text or string, leaving only a single space between words in string and no space at the start or end of the text or string.

It returns the text string where all the leading, trailing, and double spaces have been removed basically it is used to clean the texts which have irregular spaces.

TRIM Formula in Excel

Explanation of Trim Formula in Excel

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

Compulsory Parameter:

• text: it is 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 worksheet function and as VBA function.

Excel TRIM function as worksheet function.

TRIM in Excel 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).

 

TRIM in Excel 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” =TRIM(B19), the output will be “Note that the Trim function is similar”

TRIM in Excel 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.

TRIM in Excel Example #4

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

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

Excel TRIM function can be used as a VBA function.

Dim Trimtext As String

Trimtext = Trim (”   My  name  is Tanuj   “)

Msgbox(Trimtext) // Return the trimmed string in Message box.

Output will be- MY name is Tanuj

Things to Remember About the TRIM Function in Excel 

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

• Excel 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.

Recommended Articles

This has been a guide to TRIM Function in Excel. Here we discuss the TRIM Formula in excel and how to use TRIM Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• VBA TRIM in Excel
• LOOKUP Function
• POWER Function in Excel
• VLOOKUP Excel Function
• NOT Excel Function
• Concatenate Excel Function