Skip to content

How to Use Excel CLEAN Function?

Listen to this article
Clean line breaksPin
Clean line breaks

CLEAN Function in Excel: From the word CLEAN, we may understand that cleaning unwanted things in Excel. If you think so, then you are correct. Excel provides this CLEAN Function to remove extra spaces and non-printable characters from the spreadsheet so that your data should be registered in the proper way. By using this function on your worksheet, you can easily clear the line breaks inside the cells.

We all know that Excel is a powerful tool provided by Microsoft. It allows us to manage and maintain huge data on the spreadsheet. This tool has more number of built-in functions and formulas for performing mathematical operations which are difficult to do manually. In that case, when we entered long entries on your spreadsheet, there might be some unwanted spaces or line breaks hidden inside cells. And we cannot check and clear them one by one because it is hard to find them.

Today, in this article, we will give you an outline of the basic syntax, and usage of the CLEAN function in Excel. Use it to remove unwanted spaces and characters as quickly as possible. Get the official version of MS Excel from the following link:

CLEAN Function in ExcelPin
CLEAN Function in Excel

Description of CLEAN Function

  • CLEAN is one of the built-in functions provided by Microsoft Excel. And you can use this function to clear the unwanted spaces on your spreadsheet.
  • The CLEAN function always returns the text that has been cleaned of line breaks & other non-printable characters in the given text string.
  • For example, some non-printable characters are white spaces, carriage returns, tabs, line breaks, page breaks & null characters.


  • Here, you will see the syntax of the CLEAN function in Excel.
  • To apply this CLEAN function on your spreadsheet, you have to select a cell and enter the formula in the following format.
  • Once you enter the formula, just click on the Enter button to get the result.


Argument Description

  • This function has only one argument, which is your input data.
  • Text – The text string which has the line breaks or unwanted spaces on your spreadsheet.

Practical Examples

Let’s grasp some examples of the CLEAN function and explore how it works in Microsoft Excel.

  • Initially, you have to open your Excel workbook on your PC and launch a worksheet that has data.
  • For example, we entered the following information in the spreadsheet in a range B3:B5.
Input rangePin
Input range
  • In this sample data, you can see the unwanted spaces, non-printable characters, and line breaks.
  • To remove these unwanted things from the data, we need to the CLEAN function.
  • For that, we enter the above-given formula in Cell D3 and click on the Enter button.
Enter the formulaPin
Enter the formula
  • You will see that the line break in Cell B3 has been removed and stored the correct data in Cell D3.
  • For the remaining cells, you need to perform the same steps and apply the formula to get the results, as shown in the below screenshot.


This article outlined the basic syntax and usage of the CLEAN function in Excel. With the help of the given practical examples, you can easily grasp the functionality of the CLEAN function.

If you found this article helpful, then give your feedback in the below comment box. If you want to learn more about Excel functions, then visit our webpage

Read Ahead:


Leave a Reply

Your email address will not be published. Required fields are marked *

Share to...