TRIM - Remove Spaces From the Sides of Text and Extra Ones in the Middle in Excel

Add to Favorites
Author:

This allows you to make sure that there are no hidden spaces at the start or end of the text in a cell and also that there are no extra ones in the middle of the text.  In regards to extra spaces in the middle of text, it will convert all double or triple or more spaces into a single space.

Though this may seem trivial, spaces around your text can cause problems when performing other operations on a cell, such as when you use the VLOOKUP function. 

This is especially important when you have imported a large amount of data and cannot be certain that there are no extra spaces at the start or end of that data.

To solve this problem, we will use the TRIM() function.

Syntax

=TRIM(text)

ArgumentDescription
Text The actual text, surrounded by quotation marks, or a reference to a cell that contains the text from which you want to remove extra spaces.

Remove Spaces from the Start or End of Text

This is the most important thing to do when cleaning your data to make sure there are no anomalies in it so that it can be safely used in other functions within Excel.

  1. Type =trim( in the cell where you want the clean text.
  2. Select the cell that contains the text that you want to clean.
  3. Hit Enter. That's it!

You can see that the space from the start of the text was removed and, though you can't see it, the space from the end of the text was also removed.

Remove Extra Spaces from the Middle of Text

This allows you to force all spaces between text to be only one space when they could potentially have multiple consecutive spaces in them.

This is the exact same method as used above, but, here, you will see it used on data with multiple consecutive internal spaces.

  1. Type =trim( into the cell where you want the data to be.
  2. Select the data.
  3. Hit Enter.

You can see that the multiple spaces from between "my text" have now been reduced to a single space.

Notes

The TRIM function will remove the extra spaces from the middle of text and from around the sides of text at the same time.  Even though the examples above showed separate instances, it would work on text that had both problems, just like this:

This is a simple function but it is irreplaceable when you are working with large data sets, especially if you have to import them into Excel.  I always run a trim on a large data set the first time I import it into Excel just to avoid any potential problems further down the road.

Make sure to download the sample workbook for this tutorial so you can see these examples in action.


Excel Function: TRIM()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Excel Function: TRIM()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course