On this submit, we’ll present you how to remove non-printable characters in Excel. The primary 32 characters in the ASCII character desk (a regular data-encoding format for communication between computer systems) are non-printable characters. These characters aren’t displayed (or printed) however inform the applying how to format the info. Backspace (ASCII Code 08), Carriage Return (ASCII Code 13), Horizontal Tab (ASCII Code 09), and Line Feed (ASCII Code 10) are some examples of non-printable characters.
Whenever you import or paste knowledge from an exterior supply into Microsoft Excel, you might find yourself having some non-printable characters in your worksheet. Excel represents such characters as packing containers. On this submit, we’ll present you the way you might determine and clear these characters out of your Excel knowledge.
How to remove Non-printable Characters in Excel?
We are going to focus on the next two strategies to remove non-printable characters in Excel:
- Use the SUBSTITUTE() perform to remove non-printable characters
- Use the CLEAN() perform to remove non-printable characters
Allow us to have an in depth take a look at each of those strategies
Use the SUBSTITUTE() perform to remove non-printable characters in Excel
Excel provides a CODE() perform which returns the ASCII code for a given character. That is principally the reverse of the CHAR() perform which is used to translate a numerical code into a personality. When you determine the non-printable character utilizing the CODE() and the CHAR() features, you might use the SUBSTITUTE() perform to substitute (or substitute) the character with an empty string.
The syntax of the CODE() perform is:
CODE(textual content)
- The place textual content is the textual content string for which the ASCII character code (for the primary character) is required.
The syntax of the CHAR() perform is:
CHAR(quantity)
-
- The place quantity is a numeric worth between 1-255 (Prolonged ASCII character codes).
And, the syntax of the SUBSTITUTE() perform is:
SUBSTITUTE(textual content, old_text, new_text, [instance_num])
The place,
- textual content refers to the textual content string in which a substring wants to get replaced.
- old_text refers to the substring that wants to get replaced with new_text.
- new_text refers to the substring to substitute old_text with.
- [instance_num] refers to the occasion of old_text which wants to get replaced with new_text. If this argument just isn’t specified, every incidence of the old_text is changed with new_text.
Now suppose we have now a worksheet whereby we have now an instance string in cell A1, as proven in the above picture. The string comprises a non-printable character on the intense proper. To remove this character from the string, we could use the above features in the next approach:
Place the cursor in cell B1. Kind the next method in the method bar on prime:
=CODE(RIGHT(A1))
Word: For the reason that character seems on the appropriate of the unique textual content string, we have now used the RIGHT() perform to get the final character from the string after which discover its ASCII worth utilizing the CODE() perform.
Whenever you press the Enter key, the above perform will return 11, which is the ASCII code for Vertical Tab taken in this instance.
Now place your cursor in cell A2 and enter the next method:
=SUBSTITUTE(A1,CHAR(11),"")
On account of the perform, the non-printable character will likely be faraway from the unique string.
Learn: 10 Textual content features in Excel with examples.
Use the CLEAN() perform to remove non-printable characters in Excel
The CLEAN() perform in Excel removes all non-printable characters from a given textual content string. It’s the best and the most direct approach of eradicating non-printable characters in Excel.
The syntax of the CLEAN() perform is:
CLEAN(textual content)
- The place textual content represents the textual content string from which the non-printable characters want to be eliminated.
For the above instance, we could use the CLEAN() perform to remove non-printable characters in the next approach:
=CLEAN(A1)
Easy? However, the explanation, why we’re masking it later, is that it solely removes characters which have character codes ranging between 0-31 in the ASCII character desk. So it’s going to not remove non-breaking areas ( ) which will sneak in whenever you copy/paste knowledge from some exterior supply.
A non-breaking area is an area that may’t be damaged by the ‘word wrap’ function in phrase processors and different utility software program. If you would like to remove all non-printable characters in addition to the non-breaking areas from a textual content string, you want to use the CLEAN() perform, the SUBSTITUTE() perform, and the TRIM() perform collectively.
TRIM() perform can be utilized to trim areas from each ends of a given string. It’s used to repair irregular spacing in Excel.
The syntax of the TRIM() perform is:
TRIM(textual content)
- The place textual content refers to the textual content string from which the main and trailing areas want to be eliminated.
160 is the ASCII code for non-breaking area. Use the CHAR() perform to get the character worth for non-breaking area. Then use the SUBSTITUTE() perform to substitute the non-breaking area with regular area. After which use the TRIM() perform to remove all areas from each ends of the unique textual content string.
For the above instance, we could use the next method to remove non-printable characters and non-breaking areas from the unique string:
=TRIM(SUBSTITUTE(A3,CHAR(160)," "))
I hope you discover the above submit useful.
Learn Subsequent: Excel Toolbar not working.
