Editor choice

How to Remove Non-Printable Characters in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

dataset for remove non-printable characters


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.

Using CLEAN Function to remove non printable characters

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

using SUBSTITUTE function

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.

result of SUBSTITUTE function remove non-printable characters


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.

removing extra spaces remove non-printable characters

Read More: How to Remove Blank Characters in Excel


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.

using find and replace feature

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.

apply replace

Step 4: Further, click OK.

result of find and replace

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.

find and replace final result remove non-printable characters


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.

using VBA

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

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.

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.

custom function using vba

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

final result remove non-printable characters using vba

Read More: How to Remove Non-Alphanumeric Characters in Excel


Download Practice Workbook

You can download the practice workbook from here.


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.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo