Remove Non-Printable Characters in Excel [5+ Methods]

While working on an Excel spreadsheet, you’ll often find texts with non-printable characters. If you download Excel files from the internet, this tends to happen very often. The problem with non-printable characters is often you won’t see them in the cell even if they are present. And with those being present, you can’t perform any manipulation on the data. So, you’ll need to remove all the non-printable Characters from the Excel sheet. Here, I’m going to show you 6 simple methods to remove non-printable characters from Excel.

Introduction to the Dataset

Here, I’ll be using a three-column dataset. In the first column, I put non-printable characters. ASCII values from 0-31 are non-printable characters, which can be written as CHAR(25), and so on. Then, I put texts that contain those characters. Some are visible with a blank box and some are not.  In the last column, Clean Text I’ll put texts without any sort of non-printable characters.

A List of Non-Printable Characters in Excel

To remove non-printable characters, you first need to know which ones are non-printable characters. Generally, ASCII values from 00 to 31 are non-printable characters. I’m giving a list of those characters down here.

CharacterUnicode ValueDescription
NUL0Null character
SOH1Start of heading
STX2Start of text
ETX3End of text
EOT4End of transmission
ENQ5Enquiry
ACK6Acknowledge
BEL7Bell
BS8Backspace
TAB9Horizontal tab
LF10Line feed (new line)
VT11Vertical tab
FF12Form feed (new page)
CR13Carriage return
SO14Shift out (start of extended ASCII)
SI15Shift in (end of extended ASCII)
DLE16Data link escape
DC117Device control 1 (XON)
DC218Device control 2
DC319Device control 3 (XOFF)
DC420Device control 4
NAK21Negative acknowledge
SYN22Synchronous idle
ETB23End of a transmission block
CAN24Cancel
EM25End of medium
SUB26Substitute
ESC27Escape
FS28File separator
GS29Group separator
RS30Record separator
US31Unit separator

How to Type Non-Printable Characters in Excel

Now let me show you how can you insert those non-printable characters in Excel.

Syntax

=CHAR(number)&”TEXT”

Now, let’s consider our data on cell B2.

Type the following formula in that cell and press ENTER.

=CHAR(7)&”York” 

It will show the text York with a non-printable character. Here ASCII value 7 is used.

Typing Non-Printable Characters in Excel

Let’s look at another one.  In the B4 cell, it is written as

=CHAR(13)&”Bath”&CHAR(14)

Inserting Non-Printable Characters in Excel

Easiest Method to Remove Non-printable Characters in Excel

The easiest method is to use the CLEAN function. It will remove all the non-printable characters both visible and invisible.

Syntax 

=CLEAN(cell)

Usage Guide

Step_1: Type the formula on the top of the Clean Text Column.

=CLEAN(B2)

Here, cell B2 has a non-printable character. This formula will remove all non-printable characters from cell B2.

Using CLEAN Function to Remove Non-printable Characters in Excel

Step_2: Press the ENTER key.

This will show the result in that cell.

Step_3: Find the Fill Handle icon at the bottom-right corner of cell C2. Drag it down to your desired range. It will fill the rest of the column with the same formula.

Final Result

All non-printable characters are removed now as you wished.

Output of Using CLEAN Function to Delete Non-printable Characters in Excel

Note: The CLEAN function can not remove non-printable spaces.

Alternative Method #1: Using the SUBSTITUTE function

Even though the CLEAN function can not remove spaces, the SUBSTITUTE function can remove non-printable characters and extra spaces.

Syntax

=SUBSTITUTE(cell,”old_string”,”new_string”)

Usage Guide

Step_1: Type the formula on the top cell of the Clean Text column.

=SUBSTITUTE(B2, CHAR(7),"")

Using SUBSTITUTE Function to Erase Non-printable Characters in Excel


Formula Breakdown

  • B2 is the cell having non-printable characters.
  • CHAR(7) is a non-printable character which is an old_string.
  • “” is used to replace the text in cell B2 with an empty string.

Step_2: Press ENTER key. This will show the result in the cell.

Step_3: Put your cursor on the bottom right corner of cell C2 and drag it to your desired range. It will fill the rest of the column with the same formula. So all the cells containing CHAR(7) will be removed.

Here, look at the result. You’ll find all the characters have not been removed yet. This is because the formula contains CHAR(7).

Step_4: Now specify all the ASCII values in formulas  in their respective cells like this:

Similarly, for CHAR(14), it will be

=SUBSTITUTE(cell, CHAR(14),””)

For CHAR(15), it will be

=SUBSTITUTE(cell, CHAR(15),””) 

and so on.

Final Result >

Result of using SUBSTITUTE function to Delete Non-printable Characters in Excel



Alternative Method #2: Using CLEAN & SUBSTITUTE Function

While the CLEAN function and SUBSTITUTE function help you to remove non-printable ASCII characters quite well, there are some non-printable characters that don’t belong to the ASCII range. This includes numbers 127, 129, 143, 141, 144, and 157. For those characters, you can use the CLEAN and SUBSTITUTE functions both at the same time.

Syntax

=CLEAN(SUBSTITUTE(cell,”old_string”,”new_string”))

Here, The SUBSTITUTE function converts the character into an ASCII character and the CLEAN function removes them.

Usage Guide

Step_1: Type the formula on the top cell of the Clean Text column.

 =CLEAN(SUBSTITUTE(B2, CHAR(144), CHAR(7)) 

Using CLEAN and SUBSTITUTE Functions to Delete Non-printable Characters in Excel


Formula Explanation

  • B2 is the cell having non-printable characters.
  • CHAR(144) is a non-printable character which is an old_string.
  • CHAR(7) is used to replace CHAR(144).

Step_2: Press ENTER to see the result in the cell.

Step_3: Now, do the same for other cells which having non-ASCII values.

Like this,

Final Result >

Output of Using CLEAN and SUBSTITUTE Function to Delete Non-printable Characters in Excel

Alternative Method #3: Using the Nested SUBSTITUTE function

If your data has multiple non-printable characters in one cell, you can use the Multiple Substitute function nested with each other and then the CLEAN function.

Syntax

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(cell,”old_string”,”new_string”),”old_string”,”new_string”),”old_string”,”new_string”)

Usage Guide

Step_1: Type the formula on the top cell of the Clean Text column.

=(SUBSTITUTE(SUBSTITUTE(B2, CHAR(13),”” ),CHAR(14),””)

Using Nested SUBSTITUTE Function to Remove Non-printable Characters in Excel


Formula Breakdown

  • B2 is the cell having non-printable characters
  • CHAR(13) & CHAR(14) are non-printable characters that are an old_string.
  • “” is the new_string.

Step_2: Press ENTER key. This will show the result in the cell.

Step_3: Now specify all the ASCII values in formulas  in their respective cells like this:

Final Result >

Result of Using Nested SUBSTITUTE Function to Delete Non-printable Characters in Excel



Alternative Method #4: Using the TRIM & Clean Functions

Sometimes you’ll find data with extra spaces. To get rid of all the extra spaces along with non-printable characters, wrap the CLEAN Function with a Trim function.

Syntax

=TRIM(CLEAN(cell))

Usage Guide

Step_1: Type

 =TRIM(CLEAN(B2)) 

on the top cell of the Clean Text column.

Here, cell B2 has a non-printable character and extra spaces. This formula will remove all non-printable characters including spaces from cell B2.

Using TRIM and CLEAN Functions to Remove Non-printable Characters in Excel

Step_2: Press the ENTER key. This will show the result in that cell.

Step_3: Now select your desired range where you want to copy the formula and press CTRL+D. The formula will be copied to the selected area.

Final Result >

Result of Using TRIM and CLEAN Functions to Remove Non-printable Characters in Excel

Alternative Method #5: Remove Non-Printable Characters Using VBA in Excel

You can create your own User-Defined Function to remove all non-printable characters by using VBA codes. Down here I’m attaching a VBA code that works for this dataset.

Function Remove_NP_Chars(egText As String, Optional gSubText As String = " ")

Dim g As Integer
Dim gTxt
Dim gWF As WorksheetFunction

Set gWF = Application.WorksheetFunction
gTxt = Array(Chr(7), Chr(11), Chr(13), Chr(14), Chr(15), Chr(28))

For g = 1 To 31
egText = gWF.Substitute(egText, Chr(g), gSubText)
Next
For g = 0 To UBound(gTxt)
egText = gWF.Substitute(egText, gTxt(J), gSubText)
Next

Remove_NP_Chars = egText
Set gWF = Nothing

End Function

Editable Lines

gTxt = Array(Chr(7), Chr(11), Chr(13), Chr(14), Chr(15), Chr(28))

Just update the ASCII codes of the non-printable characters in this line.

This code creates a user-defined function called Remove_NP_Chars.  It just requires a cell address having non-printable characters inside it.

Syntax

=Remove_NP_Chars(cell)

Just insert the function in a blank cell with a cell address just like this:

Removing Non-Printable Characters Using Excel VBA

Copy down the function to the rest of the cells. It will remove all the non-printable characters from your set of data.

In the Clean Text column, there also have some leading and trailing spaces that are also invisible.

To remove all the leading and trailing spaces along with the non-printable characters, use this formula.

=TRIM(Remove_NP_Chars(B2))

Just copy down the formula having the TRIM function and your created user-defined function. 

The formula will remove all the non-printable characters along with leading and trailing spaces.

Conclusion

Here, I’ve tried to show you some easy methods that you can implement in Excel while working with data that has non-printable characters. Often these characters are undetectable. So, to remove them you can choose any of the methods according to your needs. I hope this will help you with your work greatly. Thank you.

Related Articles

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *