The `CLEAN` function in Excel is used to remove non-printable characters from a text string. It is especially useful when you’re working with data imported from other applications that may contain characters that do not display properly in Excel.
Syntax:
CLEAN(text)
- text: This is the required argument. It is the text string from which you want to remove non-printable characters.
Steps to Use the CLEAN Function:
- Identify the Text String: Determine the cell or text string from which you want to remove non-printable characters.
- Apply the CLEAN Function:
- Click on the cell where you want the cleaned result to display.
- Enter the formula `=CLEAN(text)`, replacing `text` with the reference to the cell containing the text you want to clean.
Example:
Suppose cell A1 contains the text “Hello!␀␀␃World”, where `␀` and `␃` are non-printable characters. To clean this text:
=CLEAN(A1)
- Click on a new cell, for example, B1.
- Enter the formula:
- Press Enter. The cell B1 will now display the cleaned text “Hello!World”, with non-printable characters removed.
Considerations:
- The `CLEAN` function is designed to remove the first 32 non-printable ASCII characters (values 0 through 31) from text. These characters often don’t have an equivalent in data imported from different sources.
- The `CLEAN` function does not remove additional spaces or printable characters that may be unwanted. For space trimming, you might use it in conjunction with the `TRIM` function.
By using `CLEAN`, you ensure that your data is devoid of potentially problematic characters that might affect text processing and display in Excel.
Unlock Your Potential
Excel
Access
Power BI
