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.

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.

Decimal Value

Hexa-Decimal Value

Character Description

0

0

NULL

1

1

START_OF_HEADING (SOH)

2

2

START_OF_TEXT (STX)

3

3

END_OF_TEXT (ETX)

4

4

END_OF_TRANSMISSION (EOT)

5

5

END_OF_QUERY (ENQ)

6

6

ACKNOWLEDGE (ACK)

7

7

BEEP (BEL)

8

8

BEEP (BEL)

9

9

HORIZONTAL_TAB (HT)

10

A

LINE_FEED (LF)

11

B

VERTICAL_TAB (VT)

12

C

FF (FORM_FEED)

13

D

CR (CARRIAGE_RETURN)

14

E

SO (SHIFT_OUT)

15

F

SI (SHIFT_IN)

16

10

DATA_LINK_ESCAPE (DLE)

17

11

DEVICE_CONTROL_1 (DC1)

18

12

DEVICE_CONTROL_2 (DC2)

19

13

DEVICE_CONTROL_3 (DC3)

20

14

DEVICE_CONTROL_4 (DC4)

21

15

NEGATIVE_ACKNOWLEDGEMENT (NAK)

22

16

SYNCHRONIZE (SYN)

23

17

END_OF_TRANSMISSION_BLOCK (ETB)

24

18

CANCEL (CAN)

25

19

END_OF_MEDIUM (EM)

26

1A

SUBSTITUTE(SUB)

27

1B

ESCAPE (ESC)

28

1C

FILE_SEPARATOR (FS) | RIGHT_ARROW

29

1D

GROUP_SEPARATOR (GS) | LEFT_ARROW

30

1E

RECORD_SEPARATOR (RS) | UP_ARROW

31

1F

UNIT_SEPARATOR (US) | DOWN_ARROW

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_

=CHAR(7)&”York” 

in that cell and press ENTER.

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

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

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

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

=CLEAN(B2)

on the top of the Clean Text Column.

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

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.

🔴Note:

But the CLEAN function can not remove non-printable spaces.

🔗 7+ Methods to Remove Characters from Left in Excel

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

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

on the top cell of the Clean Text column.

Here,

• 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

Finally, the dataset without all the non-printable characters would look like this:

🔗 5 Approaches to Remove Characters from the Right 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

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

on the top cell of the Clean Text column.

Here,

• 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

Finally, the worksheet will look like this,

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

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

on the top cell of the Clean Text column.

Here,

• 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

🔗 3+ Ways to Remove Dashes 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.

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

At last, the worksheet will look like this,

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.

🔗 A Complete Guideline to Insert and Run VBA Code in Excel

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:

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.