How to remove all non-printable characters from text strings in Excel

After you create a table in Microsoft Office Excel, non-printable characters sometimes appear in cells next to values. These are elements that cannot be typed manually from a computer keyboard, appearing as a result of errors, failures, or an incorrectly applied formula. This article will show you how to remove such characters from cells in Excel.

How to uninstall extra symbols

Such a procedure in MS Excel is usually performed using a special function built into the program. However, there are other ways to accomplish the task, which will be described in detail below.

Method 1: Using a formula

The easiest and most understandable method of uninstalling non-printing characters in Excel cells. To implement it, it is enough to follow a simple algorithm of actions:

1. Analyze the original table array, which contains “incomprehensible” characters.
2. Create an additional column in which the text cleared of nonprinting characters will be written.
3. Place the mouse cursor in the line of the second column opposite the corresponding line from the first column.
4. In the selected element, manually enter the formula “CLEAN()”. This is a standard function of the MS Excel program, designed to uninstall extra characters from tables that cannot be printed. In this example, the formula would be written as follows: “=TRIMSPACES(CLEAN(A2))”.

5. When the expression is fully written, the user will have to press “Enter” to confirm.
6. If a similar situation is observed throughout the plate, then the written formula can be extended to the remaining lines of the array so as not to write it down for each element separately, thereby saving your own time.

Pay attention! All formulas that an Excel user enters into a specific cell of the table array are also written in a special line at the top of the main menu of the program.

Method 2: Manually remove nonprinting characters

This method can only work in rare situations. However, within the framework of this article, it also needs to be considered for a complete understanding of the topic. Manual uninstallation of extra characters in Excel cells requires compliance with the following instructions:

1. Use the left mouse button to select a cell that contains non-printable characters.
2. Move the mouse cursor to the line for entering formulas, located under the Microsoft Office Excel toolbox.
3. Put the cursor after the “problem” sign and try to delete it by pressing the “Backspace” key from the PC keyboard.
4. Check result. Perhaps the icon can be removed, if not, then you should use the first method to solve the problem.
5. Perform a similar operation with all similar elements of the table array.

The method of removing non-printable characters discussed above will take the user a lot of time to implement.

Important! Function “CLEAN()” usually an argument to another function “TRIM()”, those. these two operators are used at the same time, removing both extra spaces and non-printing characters from the source text.

Method 3. Using a custom macro

A macro is a program code written in Excel’s built-in programming language. To remove unnecessary characters, hieroglyphs from specific cells of the table array, you need to follow a series of simple steps according to the algorithm:

1. Go to the Visual Basic Editor. To do this, while on the Excel worksheet, simultaneously hold down the Alt + F11 buttons from the keyboard. There are other ways to launch the editor, but they take longer to complete.

2. In the upper column of tools, click LMB on the word “Insert”.
3. In the expanded context menu, you need to click on the line “Module”.
4. After completing the previous steps, a working field for entering the code will open. At the top of the window that opens, the current name of the Microsoft Office Excel document that the PC user has set for it will be written.
5. Enter the code shown in the image below from the keyboard.

6. After entering the code, you will need to press “Enter” to run it.
7. Save the program in your macro book if necessary, close the Visual Basic menu, and check the result. If all the above actions were done correctly, then rows with non-printable characters in the original table array will be automatically deleted.

Additional Information! To save time, the program code discussed above can be found on the Internet and pasted into the Visual Basic editor window using the Ctrl + V key combination.

Method 4: Refill cells

This method is relevant if one element of the table array contains non-printable characters. Then they can be deleted and rewritten, spending the minimum amount of time to complete the task. It is possible that when the cell is refilled, the problem will be eliminated, and the original plate will take on a “normal” look. To completely uninstall the contents of a cell, it is enough to select it and press the “Backspace” or “Delete” button from the computer keyboard.

Conclusion

Thus, deleting non-printable characters in a Microsoft Office Excel spreadsheet is subject to a number of rules. The fastest way to fix the problem is with a special formula. Ways to uninstall extra characters have been discussed above. It is recommended to study them carefully for a complete understanding of the topic.