Maruf Islam
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.
Download Practice Workbook
You can download the practice workbook from here.
4 Easy Methods to Remove Non-Printable Characters in Excel
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.
Read More: How to Remove Specific Characters in Excel
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 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.
Read More: How to Remove Non-numeric Characters from Cells in Excel
Similar Readings
- How to Remove Characters from Left and Right in Excel
- Remove Single Quotes in Excel (6 Ways)
- How to Remove Semicolon in Excel (4 Methods)
- Remove Apostrophe in Excel (5 Easy Methods)
- How to Remove Parentheses in Excel (4 Easy Ways)
Method 3: Utilize Find & Replace Feature to Remove Non-Printable Characters
Find and Replace feature can find any character printable or non-printable and replace it with whatever character you choose.
Step 1: Firstly, 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.
Read More: How to Remove Non-Alphanumeric Characters in Excel (2 Methods)
Method 4: Apply VBA Macro Code to Delete Non-Printable Characters
Using VBA Macro Code, we can easily remove non-printable characters.
Step 1: Firstly, press ALT + F11 altogether to open up Microsoft Visual Basic window.
Step 2: Then, from the toolbar, Click Insert & 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
Then using RemoveNP Function, characters get replaced as string (’’ ’’).
Step 4: Further, go to the worksheet & 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 & It removes the rest of the cell’s non-printable characters.
Read More: How to Remove the First Character from a String in Excel with VBA
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 you have something to add.
