Excel CLEAN Function
The Excel CLEAN function is used to remove all nonprintable characters from text. Excel users use CLEAN function to clean text imported from other applications that contains characters that may not print with their operating system. The excel CLEAN function, for example, is used to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
List of Examples:
- What are some non-printable characters?
- How to remove line breaks with CLEAN?
- How to remove line breaks with COMMA?
- How to remove line breaks with space?
- How to generate numbers by removing non-printable characters?
- How to clean and remove extra space?
Syntax
CLEAN (text)
where,
text : The text or the reference to the cell of the text from which users want to remove non-printable characters.
Example 1: What are the some non-printable characters?
Answer: Here are examples of some non-printable characters:
White spaces (considered an invisible graphic) Carriage returns Tabs Line breaks Page breaks Null Characters
- Tabs
- Carriage returns
- White spaces
- Line breaks
- Page breaks
Example 2: How to remove line breaks with CLEAN?
Question: I have text in one cell that are in line break format. How can I remove line breaks?
Answer: The formula is =CLEAN(C6), which returns the clean text; however, the returned texts are not separated by space or comma.
Example 3: Remove line breaks with COMMA:
Question: I have texts in one cell but they are in line break format. How can I remove line break and have the text separated by commas?
Answer: The formula is =SUBSTITUTE(C6, CHAR(10), “,”), which returns the names and separates them by commas.
Example 4: Remove line breaks with space:
Question: I have texts in one cell but they are in line break format. How can I remove line break and have the text separated by spaces?
Answer: The formula is =SUBSTITUTE(C6, CHAR(10), ” “), which returns the text and separates them by spaces.
Example 5: Generate numbers by removing non-printable characters:
Question: I have numbers as well as nonprintable characters in one cell. How can I generate the numbers only?
Answer: The formula is =VALUE(CLEAN(C6)), which returns numbers and removes nonprintable characters.
Example 6: Clean and Remove extra space:
Question: How can I clean and remove extra space in text?
Answer: The formula is TRIM(CLEAN(C6)), which cleans and removes extra space.
More related text function:
Enjoyed looking at this, very good stuff, regards. “All of our dreams can come true — if we have the courage to pursue them.” by Walt Disney.
Aw, this was a really nice post. In concept I would like to put in writing like this additionally – taking time and precise effort to make an excellent article… but what can I say… I procrastinate alot and not at all seem to get one thing done.