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:
https://www.microsoft.com/en-in/microsoft-365/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.
Syntax
- 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.
=CLEAN(text)
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.
- 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.
- 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.
Closure
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 Aawexcel.com.
Read Ahead:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Deepak is an Excel enthusiast and data analyst with over 5 years of experience in the field. I’m a renowned author for AAW Excel who connects with a committed group of Excel users by imparting his professional knowledge and helpful advice. No matter your level of Excel proficiency, my articles and tutorials will teach you something new and beneficial. Connect me via my social links!… Mastodon & More!
