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 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).
Step 2: Click ENTER. It removes non-printable characters.
Step 3: Drag the Fill Handle to execute the same function throughout the range.
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
Step 1: Insert
<code>=SUBSTITUTE(C3,CHAR(11),””) in any of desired cells (D3).
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
Step 1: Insert
=SUBSTITUTE(C7,” “,””) in any cell (D7).
Step 2: Press ENTER. Characters in the reference cell get removed & displayed in the selected cell.
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.
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.
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.
Step 4: Click OK.
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 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.
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
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).
Step 5: Hit ENTER. The characters in cell C3 get removed.
Step 6: Drag the Fill Handle & It removes the rest of the cell’s non printable characters.
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.
- How to Remove the First Character from a String in Excel with VBA
- How to Remove Spaces in Excel: With Formula, VBA & Power Query
- How to Remove the Last 3 Characters in Excel
- How to Remove Non-numeric Characters from Cells in Excel
- How to Remove Characters in Excel (6 Methods)
- VBA to Remove Characters from String in Excel (7 Methods)