How to Remove Non-Printable Characters in Excel?

The sample dataset contains some non-printable characters in the Student Name column. We will try to remove these characters using the following methods.

dataset for remove non-printable characters

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

Click on any cell (D5). Enter the following formula:

=CLEAN(C5)

Click ENTER. It will remove the non-printable characters.

Using CLEAN Function to remove non printable characters

Drag the Fill Handle to execute the same function throughout the range.

result of remove non-printable characters using clean function

Note: The CLEAN function cannot remove non-printable spaces.

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

Method 2.1: For Removing Characters

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

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

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

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

Press ENTER. It will remove the non-printable characters.

using SUBSTITUTE function

Enter the specific Character Numbers inside the Formula (CHAR(7), CHAR(12), etc.).The final outcome is as shown below.

result of SUBSTITUTE function remove non-printable characters

Method 2.2: For Removing Spaces

In this formula, (” “) is replaced by character (“”).

=SUBSTITUTE(text," ","")

Insert the following formula in any cell (D9).

=SUBSTITUTE(C9," ","")

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

removing extra spaces remove non-printable characters

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

Press CTRL + H. A Find and Replace window will appear.
In the Find What command box, enter characters(Char(11), Char(7), etc.) that are present in the dataset. Click Find All.

using find and replace feature

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.

apply replace

Click OK.

result of find and replace

Repeat the steps to remove all the characters and the dataset looks as shown in the image below.

find and replace final result remove non-printable characters

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

Press ALT + F11 to open up the Microsoft Visual Basic window.
From the toolbar, Click Insert and select Module.

using VBA

Enter 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

vba macro code for removing non printable characters in excel

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.

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