Ever try to paste information into Excel from some other application?
Did you get something that looks a bit strange? Maybe like this…
Looking to get rid of the garbage that came along with the information? (I just bet you are since the other options involve a lot of work along the lines of manually editing or entering all the data…)
Anyway, that’s what I have to offer today – a way to get Excel to remove all the nonprintable characters from the text with one quick formula.
Specifically, we’re going to use the CLEAN formula to… well… clean up the data. Give this a try in a new cell(s):
=CLEAN(address of cell to clean)
Yep – that’s it. You use the formula on a set of cells and you get a new set of cells that have usable information.
I found that I could drag the formula cell contents around without loosing the cell reference (so the text stayed) and I also did a copy / paste Excel had me choose how to paste. I chose values only and ended up with text in a cell just as I’d hoped when I pasted the data in the first place!
I just wish that all cleaning could be this easy…