Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.Summary
The CLEAN function in Excel is a little-known function. As its name suggests, it cleans up text. Or more specifically, it removes all non-printable characters from text.
So wherever you see one of those ugly characters that appear as a square, this is your function. It also removes in-cell carriage returns, which saved me a lot of pain today.
Syntax
=CLEAN(Cell)
Cell is the cell containing the text string you're looking to clean.
