In this Article

*This tutorial demonstrates how to use the Excel TRIM Function in Excel to remove all extra spaces from text.*

## TRIM Function Overview

The TRIM Function Removes all extra spaces from a text string. Retains single spaces in between words.

To use the TRIM Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

### TRIM function Syntax and inputs:

=TRIM(text)

**text** – A string of text.

# How to use the TRIM Function in Excel:

The TRIM function removes all spaces in a text, except for single spaces between words.

=TRIM(B3)

## TRIM to Remove Preceding and Trailing Spaces

It is difficult to spot cells with trailing spaces (spaces behind the whole text) because text are aligned to the left by default and you won’t be able to tell there are additional spaces behind. In this eg here, it’s aligned center, so you can’t even tell that it has preceding spaces.

But if you double-click cell B3 or B4, you would see the additional spaces they have by highlighting:

## Find Number of Additional Spaces

You may want to find out if the cell has additional spaces instead of doing TRIM right away.

=LEN(B3)-LEN(TRIM(B3))

LEN helps to find the total length or number of characters. You can use that to deduct the total length of a trimmed text (removing all additional spaces).

## When TRIM Doesn’t Work

You may see obvious additional spaces in the cells, but applying TRIM doesn’t seem to work.

That’s because these are not your typical spaces. All of our characters in the keyboard has a specific ASCII (American Standard Code for Information Interchange) code to it. You could use CODE to find out what is the ASCII code of the character. For eg:

=CODE(B3)

The last one is a regular space in the keyboard and its ASCII code is 32. If you copy one of the “spaces” from where the TRIM doesn’t work and use CODE on it, it show 160:

ASCII character 160 is a non-breaking space and usually found in websites and copied to Excel. TRIM only removes character 32 (regular spaces).

To remove the non-breaking spaces __and__ regular ones, you need to combine both SUBSTITUTE and TRIM.

=TRIM(SUBSTITUTE(B3,CHAR(160),""))

SUBSTITUTE replaces all the non-breaking spaces (ASCII code 160) here with blanks and TRIM removes any additional regular spaces (ASCII code 32) if any.

# TRIM with Number/Dates

Do note that TRIM is a text function. Upon using TRIM, the result is a text. For instance, you won’t be able to sum up these numbers in cell E3 after using TRIM.

The same thing happens for dates as they are recognized as serial numbers and not text. You may not need to sum dates, but it doesn’t work well in filters and PivotTables.

To overcome the issues above, you can use VALUE to convert from text to values.

## TRIM VS CLEAN

TRIM function not applicable for non-printable characters, So if you need to remove those non-printable characters we need to use Clean. refer below table to get idea about different between TRIM and CLEAN

**Useful TRIM function examples**

You can combine TRIM with some of the Excel functions in some useful ways.

## Extract last word from String

We could combine LEFT, SUM, SUBSTITUTE with LEN as follows:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))

Which would give us the following results:

This function set for less than 10 word, so if you need to find out last word for long string you can change this number 10.

## Find Nth word in the String

We could combine MID, LEN, SUBSTITUTE, REPT with TRIM as follows to get last word of the string.

=TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))), (B2-1)*LEN(A$2)+1, LEN(A$2)))

Which would give us the following results:

## TRIM in Google Sheets

The TRIM Function works exactly the same in Google Sheets as in Excel:

## Additional Notes

When working with text, it’s always a good idea to use the TRIM Function to clean the text. Extra spaces can cause unwanted errors when working with functions such as VLOOKUP and MATCH.

You can use the EXACT Function to test if two strings of test are equal. Keep in mind, the EXACT, SUBSTITUTE, and FIND functions are case-sensitive, so it’s also a good idea to use the UPPER, LOWER, or PROPER functions to put all the text in a consistent case.

## TRIM Examples in VBA

You can also use the TRIM function in VBA. Type:

`application.worksheetfunction.trim(text)`

For the function arguments (text, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel

## How to use the TRIM Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:

`=AND(`

After entering it in the cell, notice how the AND formula inputs appear below the cell:

You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=TRIM(” into a cell, without leaving the cell, use the shortcut **CTRL + A** (**A** for **A**rguments) to open the “Insert Function Dialog Box” for detailed instructions:

For more information about the TRIM Formula visit the

Microsoft Website.