Tom’s Tutorial For Excel: TRIM and CLEAN your data
Sometimes you will receive output files from data warehouse applications, or you will copy data from a web page onto your Excel worksheet. Many cells can look jumbled with the data they hold, because of extra (and usually invisible) spacebar or ASCII characters that were not meant to be a part of the actual data.
Two functions named
CLEAN work well together to return a list of such data into a more Excel-friendly format. You can see the formula bar in the picture that with the original example data in cell A1, the formula in cell B1 is
TRIM function corrects irregular spacing by removing all spaces from text except for single spaces between words.
CLEAN function in the formula removes all non-printable characters from the text.
These two functions do not correct everything; there are some characters you’ll encounter that require other tactics to eradicate. But when it comes to excess spacebar characters (ASCII #32),
TRIM can usually do the job, and
CLEAN handles the eradication of ASCII characters 0 to 31.
To see a list of ASCII characters, in a fresh worksheet, select cell A1, enter the formula
and copy it down to cell A255.
The characters you see in cells A1:A32 will be rid by
CLEAN. At this point in typical practice, you’d copy column B and paste special for values to yield a more user-friendly list. Finally, delete column A and you are good to go.