Cleaning Up Your Data

If you ever have had problems downloading useable data into Excel from another software source, you may have wondered how to clean it up. The Good News is that there are several ways to do this! Here are a few of my favorite solutions:

First off, we have the CLEAN function. This removes the nonprinting (but often, irritating) characters, as well as other garbage characters that may have tagged along in your imported data.

Another useful function is TRIM, which eliminates any Unwanted Spaces. This is very helpful, since extra spaces are frequently imported from some sources.

Then we have one of my very favorites. The REPLACE function is quite versatile and a good tool to have at your disposal. Let’s say that you have imported data which has 5 leading unwanted characters (such as “0.00 ”). Please note that the characters may be numbers, letters, punctuation, or even spaces. If, for example, your data starts in cell A1, you could put the following formula in B1 and drag it down for the remainder of the range:

=REPLACE(A1,1,5,"")

Presto! No more unwanted leading characters. So, there you have it! Three easy-to-use methods of Cleaning Up Your Data. It is always better when things are clean!

Happy Fourth of July, All!