Home ยป Functions

TRIM Function in Excel – Remove Extra Spaces

The Excel TRIM function is an amazing formula provided by Microsoft in its excel versions to remove extra spaces in a sentence in Excel. This function is very useful to clean the data by removing more than one space between two words in excel.

This function is very useful when you import some data from another application and the data is not in a proper format.

In this tutorial, we would unlock how to use the TRIM excel function along with examples.

Let us begin ! Here we go ๐Ÿ˜Ž

When To Use TRIM Function in Excel

As mentioned in the introduction, the TRIM formula in excel is used to remove all extra spaces between words in a cell in excel. It does, however, keep a single space between the words.

To put is simply, the TRIM function removes more than one space between text, and keep only single space. Additionally, it also removes spaces at the start and end of the text.

This function belong to the text function group, and therefore the it returns a text formatted value.

Syntax and Arguments

=TRIM(text)

The one and only argument of the TRIM function is text, explained below:

  • text – In this argument, specify the text string (within double quotes) or a cell reference containing text in which you want to remove the extra spaces except one.

Examples of TRIM Excel Function Formula

In this section of the blog, let us look at some examples to understand how excel TRIM formula works.

Ex. 1 # Using TRIM Formula With Text

Below image contains some text with irregular spaces between the words.

Sample Data TRIM Function

Let us now remove the all except one spaces from these texts using TRIM formula in excel.

In cell B2, simply type the following formula and copy it to other cells.

=TRIM(A2)

As a result, excel would remove more than one space characters as shown in the image below:

TRIM Function in Excel - Example

Ex. 2 # Using TRIM Formula With Numbers

In the above example, we learned how the TRIM formula works with texts. Let us now see how to TRIM numbers using the TRIM excel function.

Sample Data –

Using TRIM with Numbers - Sample Data

To trim the numbers in the above image, simply use the following formula in cell B2 and copy it to other cells in column B.

=TRIM(A2)
Infographic - TRIM Formula Function in Excel

As a result, excel would return the following output.

TRIM Numbers Examples

In the above example, the excel keeps one space between numbers and removes all other space characters.

Remove All Spaces Between Words or Numbers in Excel

The TRIM function above removes all the additional spaces between words or numbers. However, this function keeps a single space.

Suppose now you want to remove all the spaces, such that all the words/number join each other, in the same data below:

Remove All Spaces Between Words Number Excel

To achieve this, use the below formula in cell B2:

=SUBSTITUTE(TRIM(A2)," ","")
Using TRIM with SUBSTITUTE Function Excel

Explanation – In the above example, the TRIM function removes additional spaces and keep single space. The SUBSTITUTE function then replaces single space with blank.

You may also be interested to learn about –

With this, we have reached to the end of this blog. I hope this tutorial was useful for you !!

Thank You ๐Ÿ™‚

Leave a Comment