While spaces might seem harmless from a distance, they actually cause a lot of trouble whenever you write anything. Whether it’s an article, where a lack of space or an extra space reduces the readability of an article or an Excel formula where the formula can become redundant if there is an extra space in between. One of the biggest problems with spaces is spotting them. Even the most eagle-eyed readers can sometimes overlook an extra space. In fact, while people can spot spaces between words or before the start of a sentence, it’s impossible to detect trailing spaces. They are always hidden at the end of cells. Now, if these spaces just hung out in your cells and did nothing, you wouldn’t have to worry about them. However, they can mess up the formulas that you have included in the spreadsheet. Two cells containing the same text with and without spaces, even if it’s as little as a single space character, are deemed different values. You might end up spending countless hours trying to figure out why an obviously correct formula cannot match two seemingly identical entries. If removing spaces in Excel is a problem you have faced before, this post will put an end to your troubles. Let’s get started:

Removing Spaces in Excel: All You Need to Know

Formula

This is the basic formula for removing spaces in Excel:

=TRIM(text)

Explanation 

If you need to strip leading and trailing spaces from text in one or more cells, utilize the TRIM function.

Once you’ve removed extra spaces, you can copy the cells with formulas and paste special elsewhere as “values” to get the final text. The TRIM function is fully automatic. It removes removes both leading and trailing spaces from text, and also “normalizes” multiple spaces between words to one space character only. Just supply a cell reference and watch the magic.

TRIM with CLEAN

If you also need to remove line breaks from cells, you can add the CLEAN function:

=TRIM(CLEAN(text))

The CLEAN function removes a range of non-printing characters, including line breaks, and returns “cleaned” text. The TRIM function then takes over to remove extra spaces and returns the final text.

Removing other problematic characters

CLEAN can not remove all non-printing characters, notably a non-breaking space, which can appear in Excel as CHAR(160). You can overcome that problem by adding the SUBSTITUTE function to the formula. For example, to remove a non-breaking space, you can use:

=TRIM(CLEAN(SUBSTITUTE(B1,CHAR(160),” “)))