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

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 to control various peripheral devices. Using them in Excel worksheets can cause errors. In this article, we use Functions like CLEAN, SUBSTITUTE, features like Find & Replace, and VBA Macro Code to remove non-printable characters.

Let’s say, we copy data from external sources that bear non-printable characters.

dataset

Dataset for Download

4 Easy Methods to Remove Non-Printable Characters in Excel

Method 1: Using CLEAN Function

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.

Step 1: Click on any cell (D3). Enter Formula =CLEAN(C3).

clean function

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

clean function result

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

applying fill handle

CLEAN Function is not capable of removing non-printable Spaces.

Read more: How to Remove Specific Characters in Excel

Method 2: Using SUBSTITUTE Function

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

Method 2.1: For Removing Characters

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

Step 1: Insert <code>=SUBSTITUTE(C3,CHAR(11),””) in any of desired cells (D3).

substitute function

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

result substitute

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.

removing character final

Method 2.2 : For Removing Spaces

=SUBSTITUTE (text,”  “,””)
formula, (”  “) is replaced character and (“”) replaces the character.

Step 1: Insert =SUBSTITUTE(C7,”  “,””) in any cell (D7).

removing space via substitute

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

substitute removing space final

Read more: How to Remove Special Characters in Excel

Method 3: Using Find & Replace

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

Step 1: Press CTRL+H altogether. Find and Replace window will appear.

find and replace

Step 2: In the Find What Command Box, Type Characters(Char(11), Char(7), etc.) that are present in the dataset. Click Find All; Find What Characters existing in the dataset will appear.

command in find and replace

Step 3: 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.

replace in find and replace

Step 4: Click OK.

result find and replace

Repeat the steps as many times as you need. Then all the characters get removed and the dataset looks similar to the image below

final find and replace

Read more: How to Remove Blank Characters in Excel

Method 4: Using VBA Macro Code

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

Step 1: Press ALT+F11 altogether to open up Microsoft Visual Basic window.

Step 2: From the Toolbar, Click Insert & Select Module.

microsoft visual basic

Step 3: Paste the code below in the Module

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

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

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

Step 4: Go to the worksheet & Type =RemoveNP(C3) in any cell (D3).

vba entry

Step 5: Hit ENTER. The characters in cell C3 get removed.

vba code result

Step 6: Drag the Fill Handle & It removes the rest of the cell’s non printable characters.

final vba macro code

Conclusion

CLEAN, 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 you have something to add.


Further Readings:

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo