There are so many different programs which analyse and report on data and Excel is still number one for user-friendly ways of displaying and calculating figures. When importing information from other programs or copying information in from elsewhere, it's possible that what's imported isn't all you get.
Often you may find that the data you have imported has extra spaces in it, either before or after the text in the cell. This may look OK, but if you are using a formula to search for a particular string of text, it's unlikely that you will take the extra spaces into consideration as well. This means that the formula won't work and you may not be able to locate the records that you need.
It's not just invisible spaces, though - it may also be unprintable characters that don't translate into Excel, which need to be removed from each record.
Getting rid of these unwanted spaces and symbols, or cleaning your data, makes your file work better. But (understandably) no one wants to go through each record amending every one of these. So how can you get rid of them? Luckily, there's a formula or two for that.
Removing unwanted spaces with the trim functionThe data below has been imported but there are strange spaces everywhere and some of it is formatted as uppercase and some is either mixed or lower case.
In cases like this, we can use the TRIM function to get rid of the unwanted spaces either side of the text. Combining the TRIM function with other text functions means that not only are you cleaning the data, but you're putting it into a decent format as well.
Using the Clean function to delete unidentified charactersIn the next column we would use the following formula, copied down to apply it to all the rows:
This will trim the spaces out of cells and also convert the cell to PROPER - that is, a capital for each word, which is good for names and other proper nouns. In order to delete non-printable characters, simply use the CLEAN function instead of TRIM. This will get rid of all the funny squares, circles and other unidentified symbols which are unwanted in your data.