CLEAN
The CLEAN function removes non-printable characters from a text string. It is useful for cleaning text data that includes unwanted non-visible characters. This function helps ensure data consistency and readability.
Syntax 🔗
=CLEAN(text
)
text | The text string from which non-printable characters need to be removed. |
About CLEAN 🔗
When working with textual data, you may encounter hidden non-printable characters that can affect the formatting and functionality of your content. The CLEAN function helps by removing these non-printable characters from text strings, ensuring their readability in your Excel sheets. This is particularly useful when importing data from external sources or dealing with user-generated inputs where non-printable characters may be present. By applying CLEAN to the text, you can remove any invisible characters, resulting in a clean string that works well with your Excel formulas and functions.
Examples 🔗
If you have a text string with non-printable characters, like extra spaces and line breaks, use the CLEAN function to tidy up the text. For example, if cell A1 holds the problematic text, enter the formula =CLEAN(A1) in a different cell to get the cleaned text.
When you import a dataset from a text file and discover some cells have non-printable characters, apply the CLEAN function. This helps you maintain consistency and cleanliness across your dataset.
Notes 🔗
The CLEAN function removes non-printable ASCII characters with ASCII codes from 0 to 31. It does not remove non-printable Unicode characters beyond this range.
Questions 🔗
The CLEAN function removes non-printable ASCII characters with ASCII codes 0 to 31 from the specified text string.
When is it useful to use the CLEAN function in Excel?The CLEAN function is useful when working with text data that may contain non-printable characters, such as extra spaces, line breaks, or other non-visible characters that could affect the visual presentation or functionality of the data.
Does the CLEAN function remove non-printable Unicode characters?No, the CLEAN function only removes non-printable ASCII characters with ASCII codes 0 to 31. It does not remove non-printable Unicode characters beyond this range.