It’s not uncommon to import data into Excel, either through a comma separated values (CSV) file or by copying and pasting text from another document. However, this often results in data containing extraneous spaces either before a text value, after a text value, or in the middle of the text value. But these useless spaces do not have to plague you, as they can be easily taken care of. How? You can remove spaces in Excel by using the Excel TRIM function.
You can use the TRIM function to reduce any text with more than one consecutive space where there should only be a single space in between words. This is a basic, simple function that all users of Excel should know. The TRIM function only takes one parameter:
Text: This is the text value, or cell reference, you want to remove extra spaces from.
Excel TRIM Function Example
In the sample spreadsheet below, the product status message has extra leading spaces as well as extra spaces in between words.
We can use the following TRIM function in cell A2 to replicate the product status message, but without all the extra spaces:
The result of the formula looks like this:
Check out our Excel Lesson Videos to learn how to use more text functions in Excel.
Using the TRIM function with a Literal String Value
The text parameter of the TRIM function is not limited to a cell reference as in the example above. You could also use a literal string value like this:
=TRIM(” I love learning how to use Excel functions.”)
Result: I love learning how to use Excel functions.
It’s important to note that the TRIM function does not remove every space from the supplied cell reference or literal string. It only removes additional consecutive spaces after it finds the first space.
So, the following TRIM function would return the same exact value since there are no instances of consecutive spaces in the literal string:
=TRIM(“500 Jackson Street”)
Result: 500 Jackson Street
Also, the TRIM function only removes the ASCII space character (32). Unicode text that contains other space-like characters, such as the non-breaking space character in HTML, will not be removed with the TRIM function.
As you can see, the Excel TRIM function is a quick and easy way to remove spaces in Excel and clean up your spreadsheet.