How to clean text with CLEAN and TRIM

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 might see that there’s a problem with extra space characters.

Excel contains a special text function calledTRIMthat is designed to fix this problem.

TRIM takes one argument: the text you want to process.

The result is a set of cleaned up titles without extra spaces.

On the next sheet we have a different problemline breaks that appear inside the movie titles.

Again, Excel has a function calledCLEANdesigned to fix just this problem.

Like TRIM, CLEAN takes a single argument.

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

In that case, you’re free to simply “wrap” the text in both functions.

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

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.