Formula Watch: TRIM and CLEAN your Excel data

If you import data into Excel or just have a lot of different people working in the same worksheet, you may sometimes find unnecessary spaces, numbers entered as text, and other irregularities.

Fortunately, there's a simple set of functions that can take care of most of your basic data formatting issues: TRIM and its partner CLEAN.

Independently, each can clear out unneeded spaces and other non-printing characters. But stack them for even more scrubbing power:

=TRIM(CLEAN(A2))

To put it to use, insert a column (or row) adjacen t to the one you want to clean, enter the formula, and then fill down (or across):

Notice that it converted both the text number  in A2 and general number in A3 to consistently aligned general numbers, and it also fixed the unnecessary spaces in the different text strings while leaving just once space between words.

The best part is that your original data is intact should you ever need to refer to it (though you can always right click and Hide it once you've verified that your scrubbed data is accurate and consistent.

Suzanne