Clean up your data with this easy-to-use Excel function

Excel includes a number of functions that allow you to remove printable characters, like spaces, from imported data. But what if your data contains nonprintable characters, like the ones shown in Figure A?

Microsoft MCTS Certification, MCITP Certification and over 2000+ Exams at

To clean up this data, Excel provides the CLEAN function. Follow these steps:

1. Select C2:C10.
2. Enter the following formula:
3. Press Ctrl + Enter (Figure B).

Figure B

Excel displays the contents of the cells without the nonprintable characters. You can now copy the results to another part of the workbook using the Paste Special option to copy the values without the formulas (Figure C).

Figure C

Miss an Excel tip?
Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Both comments and pings are currently closed.

Comments are closed.

A Hotels in Malta Theme. Designed by Malta Hotel and Malta Hotels