How to clean text with CLEAN and TRIM

When you bring data into Excel you sometimes end up with extra spaces and other characters that cause problems. Excel contains two functions that can help you clean things up.

Let's take a look.

Here we have a list of movie titles that were copied in from some other system. You can see that there's a problem with extra space characters.

Not only are there extra spaces between words, there are also extra spaces at the beginning and end of some of the titles.

Excel contains a special text function called TRIM that is designed to fix this problem.

TRIM takes one argument: the text you want to process. In this case, we just need to add a reference to the titles in column C, and then copy the formula down.

The result is a set of cleaned up titles without extra spaces. Notice that TRIM replaces multiple spaces between words with a single space; however, spaces at the beginning or end of the titles are completely removed.

On the next sheet we have a different problem—line breaks that appear inside the movie titles. Again, Excel has a function called CLEAN designed to fix just this problem.

Like TRIM, CLEAN takes a single argument. When I give it a reference to movie titles and copy the formula down, all line breaks are removed.

Finally, what if you have text that contains both extra spaces and non-printing characters like line breaks?

In that case, you can simply "wrap" the text in both functions. I'll first use CLEAN to remove non-printing characters, and then wrap CLEAN in TRIM to remove the extra spaces after the CLEAN function is done.

The result is a set of titles without extra spaces or line breaks.

With both of these techniques, you may want to replace the original text with the version you cleaned up. This is a good job for Paste Special.

Just copy the cleaned-up version and use Paste Special > Values to overwrite the original. Then simply delete the formulas.