Search

# How to Use Excel TRIM Function to Remove Spaces and Clean Data

## Excel TRIM Function – An Overview

### When to use Excel TRIM Function

Excel TRIM Function is best suited for situations when you need to clean the dataset byÂ removing leading, trailing, and double spaces.

### What it Returns

It returnsÂ the text string where all the leading, trailing, and double spaces have been removed.

=TRIM(text)

### Input Arguments

• Here, ‘text’ is the text stringÂ from which you want to remove the extra spaces. This could either be entered manually (within double quotes) or can be a cell reference that contains the text string. For example, if you want to remove extra spaces from the text in cell A1, then you can use the formula =TRIM(A1)

## Excel TRIM Function –Â Examples

The best way to understand how useful Excel TRIM function can be would be toÂ see it in action.

Here are three practical examples of using the TRIM function.

### Example 1 – Remove Leading, Trailing, and Double Spaces

TRIM function is made to do this.

Below is an example where there are leading, trailing, and double spaces in the cells.

You can easily remove all these extra spaces by using the below TRIM function:

`=TRIM(A1)`

Copy-paste this into all the cells and you are all set.

### Example 2 – Remove Leading Spaces Only

In some cases, you may want to get rid of only the leading spaces and not the rest.

For example, below I have a list of addresses where there is a double space between the different parts of the address (done to increase the readability). However, there are also some leading spaces (in row 2 and 4).

If you use the TRIM function on this, it will remove the leading spaces, but it will also remove the double spaces that we have intentionally placed.

Here is the formula that will remove only the leading spaces from this dataset:

`=RIGHT(A1,LEN(A1)-FIND(MID(TRIM(A1),1,1),A1)+1)`

How does this formula work?

Let me break it down for you:

• MID(TRIM(A1),1,1) – This part of the formula will give you the first character of the address (after removing the leading spaces). For example, in the case of second address (3344 Better Street), there are leading spaces, but this formula would return 3.
• FIND(MID(TRIM(A1),1,1),A1) – This part of the formula would find the position of the first character in theÂ address. For example, in the second address, 3 is in the third position (as there are two leading spaces before it).
• LEN(A1)-FIND(MID(TRIM(A1),1,1),A1)+1 – This part of the formula would give you the total length of the address after removing the leading spaces.
• =RIGHT(A1,LEN(A1)-FIND(MID(TRIM(A1),1,1),A1)+1) – This would give you the result where it extracts all the characters after the leading spaces.

On similar lines, you can create formulas that can remove only the trailing, or only leading and trailing spaces from the text.

### Example 3 – Correctly Count Number of words when there are Additional spaces

Spaces in a text is also an indication of the word count in it.

For example, if you want to count the total number of words in the phrase – “The quick brown fox jumps over the lazy dog”, you can simply count the spaces in between the words and add 1 to it.

Here is the formula you can use to count words:

`=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1`

This formula substitutes all the spaces with blanks and then counts the number of spacesÂ in the text and add 1 to it.

However, when you have additional spaces in the text, this formula would not work.

Here is the formula that you should use in such a case:

`=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1`

The TRIM part of the formula in this takes care of the additional spaces.

Related Excel Functions:

FREE EXCEL BOOK

## Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster