How to remove non-printable characters in Excel?
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 data: If you notice extra spaces in your data, it may be a sign that non-printable characters such as carriage returns or line feeds are present.
- Incorrect formatting: If your data is not formatting correctly, such as when it’s spread across multiple lines instead of being on a single line, it may be a sign that non-printable characters are present.
- Incorrect sorting: If your data is not sorting correctly, it may be a sign that non-printable characters are present. For example, if data that should be sorted together is separated by non-printable characters, it will not sort correctly.
- Incorrect data analysis: If your data is not providing accurate results when you perform analysis, it may be a sign that non-printable characters are present. For example, if data that should be treated as a single value is separated by non-printable characters, it will not be analyzed correctly.
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), ” “)
Using VBA Code
Excel’s built-in functions are effective for removing non-printable characters from small amounts of data, but for larger data sets, it can be more efficient to use VBA code. Here’s a VBA macro that you can use to remove non-printable characters from a range of cells:
Sub RemoveNonPrintableCharacters()
Dim rng As Range
Set rng = Selection
For Each cell In rng
cell.Value = Replace(cell.Value, vbCr, "")
cell.Value = Replace(cell.Value, vbLf, "")
cell.Value = Replace(cell.Value, vbTab, "")
Next cell
End Sub
To use this macro, select the range of cells that you want to clean, and then run the macro. The macro will replace all carriage returns, line feeds, and tab characters in the selected cells with a blank space.
Removing non-printable characters from data in Excel can be done using either Excel’s built-in functions or VBA code. The built-in functions are simple to use, but may not be efficient for large data sets. VBA code provides a more efficient method for removing non-printable characters from large data sets, but requires more setup and understanding of VBA programming. Regardless of the method used, removing non-printable characters can help ensure that data is clean and accurate, and that it can be used effectively in further analysis and processing.
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.
Note: To determine the ASCII code for a specific non-printable character, you can use the CODE function in Excel. For example, the ASCII code for a line feed is 10, so you can use the formula =CODE(“\n”) to determine its code.
You can also use VBA code to substitute non-printable characters in Excel. Here is an example of VBA code that replaces non-printable characters with a space:
Sub ReplaceNonPrintableCharacters()
Dim rng As Range
Dim c As Range
Set rng = Selection
For Each c In rng
c.Value = Replace(c.Value, Chr(10), " ")
Next c
End Sub
To use this code, open the VBA Editor by pressing Alt + F11, insert a new module, paste the code into the module, and then run the code. This will replace non-printable characters with a space in the selected cells. You can modify the code to replace non-printable characters with a different character if needed.
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.
Leave a Reply