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.

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.

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

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.

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.

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.

Click OK.

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

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.

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

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 (’’ ’’).