MS Excel: CLEAN function to remove non-printable characters

The CLEAN function in Excel is designed to remove all non-printable characters from a text string. It is especially useful when working with data imported from external sources such as databases, web applications, or other software systems that might contain hidden characters, line breaks, or control codes that can interfere with formulas, display, or data processing.

This function helps clean your data, making it more readable, reliable, and ready for analysis or presentation.

Syntax

=CLEAN(text)

Parameters:

• text: The text string or cell reference that may contain non-printable characters.

The CLEAN function scans the input text and removes any characters that are not printable in the ASCII character set (characters with codes 0–31). These include:

• Line breaks

• Carriage returns

• Tab characters

• Other control characters

The result is a string containing only printable characters.

The CLEAN function is focused on the first 32 non-printable ASCII characters and does not remove all Unicode non-printable characters.

Example Use Cases

Example 1: Removing Line Breaks

=CLEAN("This is line 1" & CHAR(10) & "This is line 2")

This will return:

This is line 1This is line 2

Example 2: Cleaning Imported Data

If a cell A1 contains text from an external system with hidden characters:

=CLEAN(A1)

This will remove all non-printable characters, giving you a clean, plain-text result.

Practical Applications

Use Case	Description
Cleaning imported data	Often, data from databases or web services includes control characters that disrupt formatting or calculations.
Fixing line break issues	Useful when unexpected line breaks or extra spacing appear in cells.
Preparing data for export	Ensure your text data is clean before exporting to another system or application.
Improving readability	Remove unreadable characters that may appear as boxes or symbols.

Summary

Feature	Detail
Function Name	CLEAN
Purpose	Removes non-printable characters
Returns	Cleaned text string with only printable characters
Common Pairing	Often used with TRIM, SUBSTITUTE, or TEXT

Limitations

• It does not remove extra spaces; for that, use the TRIM function.

• It does not clean all Unicode characters—only the first 32 ASCII control characters.

• May not resolve formatting issues from rich-text inputs or custom fonts.

Related Functions

Function	Purpose
TRIM	Removes extra spaces from text
SUBSTITUTE	Replaces specific characters or substrings
TEXT	Formats numbers and text in a specified format
CHAR	Returns a character from a given code (often used with CLEAN to remove specific characters like CHAR(10) for line breaks)

Final Thoughts

The CLEAN function is essential for data hygiene in Excel. Whether you're importing data from other systems or preparing it for export, using CLEAN ensures that you're working with clean, printable, and process-ready text. For best results, consider using it together with TRIM and SUBSTITUTE when dealing with messy or unstructured data.