Excel CLEAN function

Summary

The Excel CLEAN function removes non-printable characters from text. It’s used to sanitize text data by stripping out characters that might not display correctly or can cause issues in processing text.
Syntax
				
					=CLEAN(text)
				
			
  • text: The text from which you want to remove non-printable characters
Return value
Text without non-printable characters.

How to use

CLEAN is useful for preparing text data imported from other sources that may contain characters unsuitable for your Excel environment. It helps in data cleaning, especially when dealing with data from external sources like databases, web services, or text files.

Examples

Simple CLEAN
Removing Non-Printable Characters from Text: To clean text data in a cell:
				
					=CLEAN(A2)
				
			
If A2 contains text with non-printable characters, this formula returns the text without those characters.
CLEAN with CONCATENATE
Combining CLEAN with CONCATENATE: To combine and clean multiple text strings:
				
					=CLEAN(CONCATENATE(D2, E2))
				
			
Combines and cleans the text in D2 and E2, removing any non-printable characters from the concatenated result.
CLEAN for Ranges
In-depth Data Cleaning: To deeply clean a dataset with various text entries:
				
					=CLEAN(F2:F100)
				
			
Applies the CLEAN function to each cell in the range F2:F100, ensuring all text data is free from non-printable characters.

Additional Notes

  • CLEAN removes characters that are non-printable in the 7-bit ASCII code set. It might not remove all non-printable characters in text data.
  • It’s often used in combination with other text functions like TRIM and SUBSTITUTE for comprehensive data cleaning.
  • Useful in scenarios where text data needs to be standardized for consistency and accuracy.

Related Functions

Excel CLEAN function

The Excel CLEAN function removes non-printable characters from text, ensuring data cleanliness for processing and analysis.

Content Navigation