How to use TRIM Function in Excel

In this tutorial let us explore how to use TRIM Function in Excel with examples

Summary

The function is used to remove extra spaces in between words and also to remove leading and trailing spaces. This function is an inbuilt worksheet function in excel which comes under the String/Text function category which could be used as a single function or nest it into other functions. This function could also be used in a macro (Excel VBA)

Syntax:

The syntax for the function is

=TRIM (text)

Arguments or Inputs

The function requires the following arguments

text:

This argument is required

This is the string with extra spaces to remove

Result:

The function will return string with one space between words removing all extra spaces.

Example

The following screenshot shows how the function is used with different input values and the result.

Explanation based on the above screenshot

A2 is the reference to the text in cell A2 in which the input string has more spaces between words and at the end. The function removes the extra spaces between the words and the trailing space and returns the corrected string

A3 is the reference to the text in cell A3 and the input text is a mail address with leading and trailing space. The function cleans the leading and trailing spaces and returns the corrected string.

A4 is the reference to the text in cell A4 and the input is a name with extra space between the first name and the last name. The function deletes the extra spaces between the first and second name and returns the full name

A5 is the reference to the text in cell A5 in which the input is a number with leading space. The function removes the extra spaces and returns the number as text. However, you can use value function along with TRIM function to convert the text to number.

Note: The formula to remove space and convert text to number will be =VALUE(TRIM(A5))

Note: Usually when we import data from an external source, we may get words with extra in-between spaces and number as text. In such case Trim along with other TEXT functions like CLEAN, FIND, SUBSTITUTE, VALUE may come handy.

Question: How to identify a number with text format.

Answer: In Excel numbers are aligned to the right of the cell and the text are aligned to the left of the cell. By looking at the alignment you can identify the number with text format. Also, a math function like SUM, AVERAGE will return error if applied on number with text format.

Question: What if I want to remove all the spaces in a range of cells like the one shown below.

Answer: Its very simple to remove all the spaces in a range of cells. Select the range. Press Ctrl+H, this will open the “Find and Replace” dialog box. In the “Find What” box enter a space by pressing the space bar once. Don’t enter anything in the “Replace With” box. Press “Replace All” button. All the space in the range will be cleared.

This function is also available in VBA with the same functionality along with two more trim functions, LTRIM and RTRIM. LTRIM is used to remove the leading spaces and RTRIM is used to remove the trailing spaces.