How to Remove Spaces in Microsoft Excel

"Trim the fat" is a phrase many of us use, and it applies to Excel as well.

There are so many different programs which analyse and report on data, and Excel is still the number one application for user-friendly ways of displaying and calculating figures. When importing information from other programs or copying information in from elsewhere, it's possible that the data that you're wanting isn't all that you get.

Often you may find that the data you have imported has extra spaces in it, either before or after the text in the cell. This may look OK, but if you are using a formula to search for a particular string of text, it's unlikely that you will take the extra spaces into consideration as well. This means that the formula won't work and you may not be able to locate the records that you need.

It's not just invisible spaces, though - it may also be unprintable characters that don't translate into Excel, which need to be removed from each record.

Getting rid of these unwanted spaces and symbols, or cleaning your data, makes your file work better. But (understandably) no one wants to go through each record amending every one of these. So how can you get rid of them? Luckily, Excel has a formula or two for that.

Removing unwanted spaces with the trim function

The data below has been imported from an external source, but there are strange spaces everywhere, and some of it is formatted as uppercase and some is either mixed or lower case.

In cases like this, we can use the TRIM function to get rid of the unwanted spaces either side of the text. Combining the TRIM function with other text functions means that not only are you cleaning the data, but you're putting it into a decent format as well.

Using the Clean function to delete unidentified characters

In the next column we would use the following formula, copied down to apply it to all the rows:

=PROPER(TRIM(A1))

This will trim the spaces out of cells and also convert the cell to PROPER - that is, a capital for each word, which is good for names and other proper nouns. In order to delete non-printable characters, simply use the CLEAN function instead of TRIM. This will get rid of all the funny squares, circles and other unidentified symbols which are unwanted in your data.