Software

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

Here's a truly efficient way to get rid of garbage characters that have tagged along with imported data: Just let the Excel CLEAN function remove them for you.
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?

Figure A

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

  1. Select C2:C10.
  2. Enter the following formula: =CLEAN(A2)
  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.

3 comments
trueseek1
trueseek1

Thanks. It finally worked. Took a few times thinking I got it, but failed every time. Did not give up. finally figure out that the picture you got is what's needed, not to enter the cell numbers in the formula and definitely do not enter cell number of the 1st line that includes the field name/title bar of the column. Once I picked ONLY the cell A2 (literally), and followed your instructions, all was well. Hours saved on a job. Corrections do need to be reviewed since LLC might come out Llc or some names might come out looking wrong, but reviewing and fixing for a few minutes using this formula avoided many misspellings that would have occurred in retyping the whole database names, and saved us several hours of work. Thank you!

odedf
odedf

Tried to do but did not work the non printable characters are still there???????

gedwards
gedwards

If you enter formula in B2, then go to the lower right corner and double click, it will fill a continuous range. Useful for larger data sets. This is a common cause of VLookup not working when pulling data from multiple sources together. Sometimes it can also be spaces you need to trim off. Greg E

Editor's Picks