How to Remove Non-Printable Characters in Excel?

Every character printable or non-printable bears a Unicode Character Code or Value. The first 32 characters (0-31) of Unicode or ASCII in programs are used to control various peripheral devices. Using them in Excel worksheets can cause errors. In this article, we will use functions like CLEAN, SUBSTITUTE, and features like Find and Replace, and VBA Macro Code to remove non-printable characters in Excel.

How to Remove Non-Printable Characters in Excel: 4 Easy Ways

To explain the methods, we will use a dataset that contains some non-printable characters in the Student Name column. We will try to remove these characters using the following methods.

Method 1: Use CLEAN Function to Remove Non-Printable Characters

The CLEAN function can remove non-printable characters 0-31 in ASCII or Unicode. If you want to remove numbers beyond that range, the SUBSTITUTE function is a handy alternative. You can see the use of the SUBSTITUTE function in Method 2.

Step 1: Click on any cell (D5). Enter Formula:

=CLEAN(C5)

Step 2: Click ENTER. It removes non-printable characters.

Step 3: Drag the Fill Handle to execute the same function throughout the range.

Note: The CLEAN function is not capable of removing non-printable spaces.

Method 2: Insert SUBSTITUTE Function to Erase Non-Printable Characters

The SUBSTITUTE function can remove a wide range of non-printable characters and spaces.

Method 2.1: For Removing Characters

Formula, replaces CHAR(), with an empty text (“”).

=SUBSTITUTE(text,CHAR(),"")

Step 1: Insert the following formula in any of the desired cells (D5).

=SUBSTITUTE(C5,CHAR(11),"")

Step 2: Press ENTER. Execution of the steps removes non-printable characters.

Step 3: Type the specific Character Numbers inside the Formula (CHAR(7), CHAR(12), etc.) individually following pressing ENTER. The final outcome is similar to the image below.

Method 2.2: For Removing Spaces

The following formula, (” “) is replaced by character (“”).

=SUBSTITUTE(text," ","")

Step 1: Insert the following formula in any cell (D9).

=SUBSTITUTE(C9," ","")

Step 2: Press ENTER. Characters in the reference cell get removed & displayed in the selected cell.

Method 3: Utilize Find & Replace Feature to Remove Non-Printable Characters

The Find and Replace feature can find any character printable or non-printable and replace it with whatever character you choose.

Step 1: First, press CTRL + H altogether. A Find and Replace window will appear.

Step 2: Then, in the Find What command box, type characters(Char(11), Char(7), etc.) that are present in the dataset. Click Find All to move forward.

Step 3: Afterward, in the Replace With command box, Type Char(32), which is the representation of the SPACE character. Click Replace All; Non-printable characters get replaced by SPACE characters.

Step 4: Further, click OK.

Step 5: Finally, repeat the steps as many times as you need. Then all the characters get removed, and the dataset looks similar to the image below.

Method 4: Apply VBA Macro Code to Delete Non-Printable Characters

Using VBA Macro Code, we can easily remove non-printable characters.

Step 1: First, press ALT + F11 altogether to open up the Microsoft Visual Basic window.

Step 2: Then, from the toolbar, Click Insert and select Module.

Step 3: Afterward, paste the code below in the Module window:

Function RemoveNP(mText As String, Optional mSubText As String = " ")
Dim I As Integer
Dim MyText
Dim mWF As WorksheetFunction
Set mWF = Application.WorksheetFunction
MyText = Array(Chr(7), Chr(11), Chr(12), Chr(15))
For I = 1 To 31
mText = mWF.Substitute(mText, Chr(I), mSubText)
Next
For I = 0 To UBound(MyText)
 mText = mWF.Substitute(mText, MyText(J), mSubText)
Next
ReplaceNP = mText
Set mWF = Nothing
End Function

In the code, we declared a function RemoveNP with two potential parameters. Here, we’ve declared characters that may exist in our dataset or exact characters (Char(7), Char(11), etc.) that exist in our dataset.

Then using RemoveNP Function, characters get replaced as string (’’ ’’).

Step 4: Further, go to the worksheet and type the following formula in any cell (D5).

=RemoveNP(C5)

Step 5: After that, hit ENTER. The characters in cell C5 get removed.

Step 6: Finally, drag the Fill Handle and it removes the rest of the cell’s non-printable characters.

Download Practice Workbook

You can download the practice workbook from here.

Remove Non-Printable Characters.xlsm

Conclusion

In this article, we have demonstrated 4 easy methods to remove non-printable characters in Excel. The CLEAN and SUBSTITUTE functions remove non-printable characters with ease. Though the CLEAN function has its own limitations, the SUBSTITUTE function or Find & Replace works for pretty much all characters. Declaring Characters in VBA Macro Code it’s never been so handy to remove all kinds of non-printable characters. Hope you find the above-discussed methods super easy to use. Comment, if you need further clarifications, or if you have something to add.

<< Go Back To Excel Remove Characters | Data Cleaning in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!