Remove Non-Printable Characters in Excel.
CLEAN Function Overview
When data files are imported or copied into Excel worksheet non-printable characters can sometimes be included along with the good data.
A common example of these non-printable characters is the small box shaped character seen in cell D1 in the image above.
The CLEAN function can be used to remove most of these non-printable characters.
ASCII Character Codes
Each character on a computer – printable and non-printable – has a number known as its ASCII code or value.
ASCII stands for the American Standard Code for Information Interchange and it encompasses one set of codes for 255 characters and symbols for use in computer programs.
The first 32 characters (0 to 31) of the ASCII set are referred to as control characters and they are used by programs to control peripheral devices such as printers.
As such, they are not intended for use in a worksheet and they cannot be printed. In addition, they also create errors in Excel formulas if they are present in a cell along with good data.
The CLEAN Function’s Syntax and Arguments
A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments.
The syntax for the CLEAN function is:
= CLEAN ( Text )
Text – (required) the data from which you want to remove non-printable characters. Although the argument can contain the actual data, it can also be a cell reference to the location of the data in the worksheet.
Example: Remove Non-Printable Characters with the CLEAN Function
The image above shows a box-shaped non-printable character (ASCII code #12) along with the number 10 in cell D1 of an Excel worksheet.
Cell F1 contains a formula that is attempting to add the contents of cells D1 + E1, but the presence of the non-printable character in D1 has resulted in a #VALUE! error value appearing in cell F1 rather than the formula answer.
This example will go through the steps necessary to remove the non-printable character with the CLEAN function as seen in row two of the image.
Entering a Non-Printable Character into a Worksheet
In order to have a non-printable character to remove, the first step will be to enter one into the worksheet using the CHAR function. This function converts an ASCII code into an actual character.
- Enter the following data into cell D1
=CHAR(12) & “10” – this enters the non-printable character next to the number 10 - If the box-shaped character is not present next to the number, as shown in the image above, change the font for cell D1 to Verdana – some fonts do not display non-printable characters
- Enter the number 15 into cell E1
- Click on cell F1 and enter the formula = D1 + E1
- Press the Enter key on the keyboard to complete the formula
- The #VALUE! error value should appear in cell F1 due to the presence of the non-printable character in cell D1
Entering the CLEAN Function
- Click on cell D2 to make it the active cell
- Click on the Formulas tab of the ribbon menu
- Choose Text from the ribbon to open the function drop down list
- Click on CLEAN in the list to bring up the function’s dialog box
- In the dialog box, click on the Text line
- Click on cell D1 in the worksheet
- Click OK
- The number 10 should appear in cell D2 on its own
- If you click on cell D2 the complete function = CLEAN ( D1 ) appears in the formula bar above the worksheet
Entering the Addition Formula
- Enter the number 15 into cell E2
- Click on cell F1 and enter the formula = D1 + E1
- Press the Enter key on the keyboard
- The number 25 – the answer to the formula – should appear in cell F2 now that the non-printable character has been removed
The CLEAN Function and Other Non-Printable Characters
As mentioned above, the CLEAN function was designed to remove the first 32 non-printable ASCII characters from a worksheet.
There are additional non-printable ASCII characters – #127, #129, #141, #143, #144, and #157 – which, according to the Microsoft help file, cannot be removed by the CLEAN function alone.
In actuality, however, the CLEAN function does remove ASCII characters #129, #141, #143, #144, and #157, which leaves #127 as the only non-printable character that the function cannot remove.
ASCII #127 is the code for the delete key on the keyboard, and, while it cannot be removed by the CLEAN function, it can be removed using a formula containing the SUBSTITUTE and CHAR functions.
Removing Non-Breaking Spaces from a Worksheet
Similar to non-printable characters is the non-breaking space ( ) which can also cause problems with calculations and formatting in a worksheet. The ASCII code for non-breaking spaces is #160.
Non-breaking spaces are used extensively in web pages, so if data is copied into Excel from a web page, non-breaking spaces may show up in a worksheet.
This tutorial on removing non-breaking spaces explains how to remove them with a formula that combines the SUBSTITUTE, CHAR, and TRIM functions.
Also read 20 Tricks That Can Make Anyone An Excel Expert