Summary
When you bring data into Excel you sometimes end up with extra spaces and line breaks. This short video shows you how to clean things up with TRIM and CLEAN.
Video Transcript
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 movies titles that were copied in from some other system. You can see 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, 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. Hoever, spaces at the beginning or and of the text 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 for just this problem.
Like TRIM, CLEAN takes a single argument. When I give it a reference to the movie titles and copy the formula down, all line breaks are removed.
Technically, the CLEAN function removes only the first 32 nonprinting characters in 7-bit ASCII. There are situations where you'll need to use more advanced techniques to remove non-printing characters.
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, then wrap CLEAN in TRIM to remove extra spaces after TRIM 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 orignal. Then simply delete the formulas.
Author
Dave Bruns
source: excel(dot)net
