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
To determine if cleaning of non-printable characters in Excel is necessary, you should examine your data for the presence of any non-printable characters. Some common symptoms of the presence of non-printable characters in your data include:
- Extra spaces in the data.
- Incorrect formatting, like data spread across multiple lines.
- Sorting problems, where similar data is not grouped correctly.
- Inaccurate data analysis, where non-printable characters cause incorrect results.
You can check whether cell cleaning is needed by using 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
Using CLEAN function
To remove non-printable characters, use the CLEAN function. Syntax is:
=CLEAN(text)
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.
or
=CLEAN(A1:B10) for the whole range of cells
Using TRIM function
The TRIM function removes leading and trailing spaces from text. However, it does not remove non-printable characters such as carriage returns, line feeds, or tab characters. To remove these characters, you can use the TRIM function in combination with the SUBSTITUTE function.
Example: To remove all non-printable characters from cell A1, you can use the following formula: =TRIM(SUBSTITUTE(A1, CHAR(9), ” “))
Using SUBSTITUTE function
The SUBSTITUTE function replaces specified characters in text with other characters. In this case, you can use it to replace non-printable characters with a space.
Example: To remove all non-printable characters from cell A1, you can use the following formula: =SUBSTITUTE(A1, CHAR(9), ” “)
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 > 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.
