Trim Function in Excel

TRIM Formula in Excel

The TRIM formula in Excel has only one critical parameter: text.

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

How to Use TRIM Function in Excel?

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

Example #1

In this example, the TRIM function in Excel removes the extra spaces from the given texts/ words at the beginning and end.

And the result will be in the second column. So, for example, applying TRIM on “Tanuj,” the output will be “Tanuj” =TRIM(B5).

Example #2

In this example, the trim removes the extra spaces from the middle of 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 are extra spaces between the string, it will not work, so we use trim to achieve it. Here we use the =LEN(TRIM(B32))-LEN(SUBSTITUTE(B32,” “,”))+1 function. It will remove extra spaces, then count the number of spaces and add one to give the total number of words in the string.

Example #4

We can use Excel’s TRIM and substitute function in excelSubstitute Function In ExcelSubstitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.read more to join the multiple columns separated by a comma.

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

Things to Remember

The TRIM function removes extra spaces from the text, leaving no extra space between words and no beginning and end space at the string.

  • The TRIM function can only remove the ASCII space character (32) from text/string.The TRIM function in Excel cannot remove the Unicode text that often contains a non-breaking space character (160) that appears on web pages as the HTML entity.The TRIM function in Excel is very useful for cleaning the text from other applications or environments.

Trim in Excel Video

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

  • VBA TRIM in ExcelVBA TRIM In ExcelVBA TRIM is classified as a string and text function. This is a worksheet function in VBA that, like the worksheet reference, is used to trim or remove unwanted spaces from a string. It takes a single argument, which is an input string, and returns an output string.read morePOWER FunctionNOT Excel FunctionConcatenate in ExcelPercentage Change Formula in ExcelPercentage Change Formula In ExcelA percentage change formula in excel evaluates the rate of change from one value to another. It is computed as the percentage value of dividing the difference between current and previous values by the previous value.read more