Cleaning your data is often an important pre step to actually using it to make decisions. Three useful functions in Excel to help do that are TRIM, CLEAN, and PROPER.
TRIM(text)
The TRIM function will remove all spaces from text except for single spaces between words. You would be surprised how often people put extra spaces in, either between words, or at the end of what they have typed. And it can be very hard to see an extra space at the end of a word, but when counts or lookups don’t work, an extra space is often the problem.
TRIM, takes in a text and removes those extra spaces.
CLEAN has the same input as TRIM, some text, and it removes all nonprintable characters from text. “Non printable characters” is a bit technical but sometimes application use things like CHAR(10) to mean ‘start a new line’, and sometimes these show up in your spreadsheet, and it is nice to be able to remove them.
PROPER also has the same input requirements, some text (or reference to a cell that has text in it).
PROPER capitalizes the first letter in a text string and any other letters in the text that follow any character other than a letter. Converts all other letters to lowercase letters.
For example, PROPER(“this iS a mEss”) would return This Is A Mess.
You might also want to check out the top ten ways to clean your data.
Now, you can TRIM, CLEAN, and PROPER your text data until it is sparkly clean and ready to use.