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.
=TRIM(text)
Argument | Description |
---|---|
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. |
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.
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.
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.
You can see that the multiple spaces from between "my text" have now been reduced to a single space.
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.