Removing Hidden Characters with CLEAN

After removing extra spaces, another common issue in text data is hidden (non-printable) characters.

These characters are not visible in Excel, but they often appear when you:

• Copy data from websites;
• Import CSV files;
• Work with exported system data.

For example, a cell may contain tabs, line breaks, or other invisible symbols. Visually, the text looks normal, but Excel treats it differently. This can break formulas, comparisons, and text processing.

Example

At first glance, everything looks normal. However, the first value contains a tab between words, and the second one contains a line break inside the cell. These hidden symbols can break logic when working with data.

The symbols \t and \n represent special characters: \t means a tab (a large space between words), and \n means a new line (a line break inside the same cell).

CLEAN Function

To fix this, Excel provides the CLEAN function.

This function removes all non-printable characters from text. In other words, it clears out invisible symbols and leaves only readable content.

=CLEAN(A2)

When you apply CLEAN, Excel processes the text and removes hidden characters.

Create a new column (for example Clean Text), and apply the CLEAN function to the values from the original column.

You can choose any column from the main dataset that may contain hidden characters. Then fill the formula down so that all rows are processed and cleaned.

Hidden characters are not visible, so if your text looks clean but behaves incorrectly, try applying CLEAN() to remove them.