In this lesson, you will learn how to remove non-printable characters in Excel with the clean and substitute functions.
It may happen that data copied from external websites will be pasted with non-printable characters. This lesson lets you clean up your spreadsheet. You can't print such characters. It looks really unprofessional.
How to identify if cleaning is needed
First, you can check whether cell cleaning is needed. You can use this formula for checking:
=IF(CLEAN(A1)=A1,"Cleaning NOT needed","Cleaning is needed")
The formula will indicate if cleaning is required for your cell.
How to remove non printable characters
To remove non-printable characters, use the CLEAN function. Syntax is:
You remove non-printable characters. You can also use the address of a cell phone. The syntax in that condition is:
=CLEAN(A1) for a single cell.
=CLEAN(A1:B10) for the whole range of cells
How to substitute non printable characters
If you know which nonprinting character you want to remove, you can do so with the Substitute function.
The Substitute function is used to replace characters. In this case, you're going to replace a specific nonprinting character with nothing. So you're just going to remove this mark.
Have yourself a specific identifier for the character you want to remove. Make it CHAR123.
Create a substitute formula like this: =SUBSTITUTE(A1,CHAR(123), "")
Using this substitute formula will remove CHAR123 from cell A1.
How to remove spaces
Removing of extra spaces using Substitute formula
The Substitute function can also be used to remove redundant spaces.
Use the already known scheme. Replace unnecessary spaces with nothing. This will remove extra spaces.
The Substitute formula you will use is =SUBSTITUTE(A1, "", "")
This formula removed spaces from cell A1.
Removing extra spaces with Find and Select
Find and Select is an Excel feature that can help you. Using Find and Select you can remove excess spaces.
Go to Ribbon> Home > Find and Select > select Replace
In the Find what field, enter a space. Do not enter anything in the Replace with field.
Click Replace All to remove spaces from your worksheet.
After that, you will be able to print as expected.