Introduction
The CLEAN function in Excel helps remove non-printable characters from text. These hidden characters often appear when you copy data from websites, software, databases, or external reports.
For example, an employee name or customer address may contain hidden line breaks or unwanted characters. These characters can affect how your data looks and make it harder to maintain clean records.
In this tutorial, you will learn how to use the CLEAN function with practical Excel examples.
CLEAN Function Syntax
=CLEAN(text)
Argument:
- text: The text or cell reference from which you want to remove non-printable characters.
Important: CLEAN removes the first 32 non-printable ASCII characters, including certain hidden line breaks. It does not remove ordinary extra spaces or every special character.
Question 1: Remove Hidden Characters from Employee Names
Problem Statement:
An HR department receives employee names from an external system. Some names contain hidden characters that affect the appearance of the data.
Use the CLEAN function to remove these characters.
Practical Data:
| Employee ID | Employee Name |
|---|---|
| EMP101 | Amit Sharma |
| EMP102 | Priya Verma |
| EMP103 | Rahul Kumar |
| EMP104 | Neha Singh |
Assume the employee names are in cells B2. Some cells contain hidden non-printable characters.
Excel Formula:
Enter this formula in C2:
=CLEAN(B2)
Copy the formula down to C5.
Expected Result:
| Employee ID | Original Name | Cleaned Name |
|---|---|---|
| EMP101 | Amit Sharma | Amit Sharma |
| EMP102 | Priya Verma | Priya Verma |
| EMP103 | Rahul Kumar | Rahul Kumar |
| EMP104 | Neha Singh | Neha Singh |
Explanation:
The CLEAN function removes non-printable characters from the employee names. If a cell does not contain any such characters, its visible text remains unchanged.
Question 2: Clean Imported Customer Data
Problem Statement:
A sales team imports customer names from an external application. Some customer records contain hidden characters.
Use CLEAN to prepare the customer names for further use.
Practical Data:
| Customer ID | Customer Name |
|---|---|
| CUST201 | Amit Enterprises |
| CUST202 | Bright Solutions |
| CUST203 | Global Traders |
| CUST204 | Smart Technologies |
Assume customer names are stored in B2.
Excel Formula:
=CLEAN(B2)
Enter the formula in C2 and copy it down.
Expected Result:
| Customer ID | Cleaned Customer Name |
|---|---|
| CUST201 | Amit Enterprises |
| CUST202 | Bright Solutions |
| CUST203 | Global Traders |
| CUST204 | Smart Technologies |
Explanation:
CLEAN removes hidden non-printable characters from imported customer names. This helps maintain cleaner customer records.
Question 3: Remove Hidden Line Breaks from Product Details
Problem Statement:
A product list is copied from another application. Some product descriptions contain hidden line breaks, causing text to appear on multiple lines.
Use CLEAN to remove non-printable line-break characters.
Practical Data:
| Product ID | Product Details |
|---|---|
| P101 | Wireless Keyboard |
| P102 | USB Mouse |
| P103 | Laptop Stand |
| P104 | Bluetooth Speaker |
Assume product details are stored in B2. Some cells contain hidden line-break characters.
Excel Formula:
=CLEAN(B2)
Enter the formula in C2 and copy it down.
Expected Result:
| Product ID | Cleaned Product Details |
|---|---|
| P101 | Wireless Keyboard |
| P102 | USB Mouse |
| P103 | Laptop Stand |
| P104 | Bluetooth Speaker |
Explanation:
CLEAN removes non-printable line-break characters from the product descriptions. The cleaned text is easier to read and manage in the worksheet.
Question 4: Clean Imported Invoice Records
Problem Statement:
The accounts department receives invoice descriptions from accounting software. Some descriptions contain hidden characters.
Use CLEAN to remove these characters.
Practical Data:
| Invoice No. | Invoice Description |
|---|---|
| INV-101 | Website Development |
| INV-102 | Software Training |
| INV-103 | Annual Maintenance |
| INV-104 | Technical Support |
Assume invoice descriptions are stored in B2.
Excel Formula:
=CLEAN(B2)
Enter the formula in C2 and copy it down.
Expected Result:
| Invoice No. | Cleaned Description |
|---|---|
| INV-101 | Website Development |
| INV-102 | Software Training |
| INV-103 | Annual Maintenance |
| INV-104 | Technical Support |
Explanation:
CLEAN removes non-printable characters from invoice descriptions. This is useful when working with data copied from external accounting systems.
Question 5: Clean an Employee Database
Problem Statement:
An HR department receives employee records from different sources. The employee names, departments, and designations may contain hidden non-printable characters.
Use the CLEAN function to prepare the records.
Practical Data:
| Employee ID | Employee Name | Department | Designation |
|---|---|---|---|
| EMP501 | Amit Sharma | HR | HR Executive |
| EMP502 | Priya Verma | Sales | Sales Manager |
| EMP503 | Rahul Kumar | IT | Software Developer |
| EMP504 | Neha Singh | Accounts | Accountant |
Assume the data is stored in A2.
Excel Formulas:
For employee names, enter in E2:
=CLEAN(B2)
For departments, enter in F2:
=CLEAN(C2)
For designations, enter in G2:
=CLEAN(D2)
Copy the formulas down to row 5.
Expected Result:
| Employee ID | Cleaned Name | Cleaned Department | Cleaned Designation |
|---|---|---|---|
| EMP501 | Amit Sharma | HR | HR Executive |
| EMP502 | Priya Verma | Sales | Sales Manager |
| EMP503 | Rahul Kumar | IT | Software Developer |
| EMP504 | Neha Singh | Accounts | Accountant |
Explanation:
CLEAN removes non-printable characters from each selected text column. This helps prepare employee records for reporting and data management.
Student Practice Tasks
Try these exercises yourself using the CLEAN function.
Task 1: Employee Records
Remove hidden characters from employee names stored in cells B2.
Task 2: Customer Database
Clean customer company names stored in cells C2.
Task 3: Product List
Remove non-printable characters from product descriptions stored in cells B2.
Task 4: Invoice Data
Clean invoice descriptions stored in cells D2.
Task 5: Department Records
Clean department names stored in cells C2.
Final Student Challenge: Clean a Complete Company Database
Problem Statement:
You are working as an HR data executive. Your company has received employee information from an external system. Some fields contain hidden non-printable characters.
Your task is to clean the employee names, departments, and job titles using only the CLEAN function.
Practice Data:
| Employee ID | Employee Name | Department | Job Title |
|---|---|---|---|
| EMP701 | Arjun Mehta | Finance | Accountant |
| EMP702 | Sneha Kapoor | HR | HR Manager |
| EMP703 | Karan Malhotra | IT | Web Developer |
| EMP704 | Pooja Sharma | Sales | Sales Executive |
| EMP705 | Rohan Gupta | Operations | Operations Manager |
Your Task:
- Clean all employee names.
- Clean all department names.
- Clean all job titles.
- Store the cleaned information in separate columns.
- Check the final employee database.
Expected Result:
A clean employee database with no non-printable characters in the selected text fields.
Key Takeaways
- CLEAN removes non-printable characters from text.
- It is useful for cleaning imported data.
- It can remove certain hidden line breaks.
- It works with both direct text and cell references.
- It does not remove ordinary extra spaces.
- It does not remove every special character.
- CLEAN is useful when preparing employee, customer, product, and invoice records.
Frequently Asked Questions (FAQs)
1. What is the CLEAN function in Excel?
The CLEAN function removes non-printable characters from text. It is commonly used to clean imported or copied data.
2. What is the syntax of the CLEAN function?
The syntax is:
=CLEAN(text)
The text argument contains the text or cell reference you want to clean.
3. Does CLEAN remove extra spaces in Excel?
No. CLEAN removes non-printable characters, not ordinary extra spaces between words or at the beginning and end of text.
4. Can CLEAN remove line breaks in Excel?
Yes. CLEAN removes certain non-printable line-break characters, including carriage returns and line feeds.
5. Does CLEAN remove special characters?
No. CLEAN does not remove all special characters. Visible characters such as @, #, $, and % generally remain.
6. Can I use CLEAN in Excel with a cell reference?
Yes. For example:
=CLEAN(A2)
This removes non-printable characters from the text in cell A2.
7. Does CLEAN in excel change the original cell value?
No. CLEAN returns the cleaned text in the cell containing the formula. The original cell remains unchanged unless you replace its contents with the result.
8. Is CLEAN useful for imported Excel data?
Yes. It helps remove certain hidden characters that may appear when data is copied from websites, databases, and external applications.
9. Does CLEAN remove non-breaking spaces?
No. The non-breaking space, commonly represented by character code 160, is not removed by CLEAN.

