To remove nonprinting characters from text in Excel, enter =CLEAN(A2) in a helper cell when the source text is in A2. CLEAN removes certain control characters from the text; it does not remove ordinary extra spaces or every invisible character. You can fill the formula down a column, check the results, and keep the cleaned text in a new column or paste it back as values.
What You’ll Need
- An Excel workbook with the text you want to clean. These steps are for Excel for Microsoft 365 on Windows; CLEAN is also available in Excel for Mac and Excel for the web.
- An empty column beside your source data for the formula results. Keeping the original data intact makes it easier to check your work and recover if the output is not what you expected.
- A basic idea of which characters are causing trouble, if you have one. For example, imported text may contain line breaks or control characters that are not visible in the cells.
Step-by-Step
- Make a safe working copy. If the data is important, save a copy of the workbook or duplicate the worksheet before changing it. CLEAN can change how text appears and behaves, and replacing the original values is not always easy to reverse after you save and close the file. Keep the source column until you have checked the results.
- Identify the source text and choose a helper column. For example, if the text to clean is in A2:A100, use the blank column next to it, such as B. Add a heading such as “Cleaned text” in B1 so the output is easy to distinguish from the original. Do not put the formula over the source value: it would replace the input the formula needs to read.
- Enter CLEAN in the first output cell. With the original text in A2 and the result cell in B2, enter
=CLEAN(A2)in B2 and press Enter. CLEAN takes one text argument: the cell or text value you want it to process. It removes the first 32 nonprinting control characters in the 7-bit ASCII set, whose character codes run from 0 through 31. This can help with text copied from other systems or imported into Excel. - Check what the function changes. If A2 contains the word North, a line break, and then the word wind, CLEAN removes the line-break character. The result is Northwind, not North wind, because CLEAN removes the control character rather than replacing it with a space. Check several examples, especially cells where a control character might separate words or fields, before applying the formula to a long list.
- Fill the formula down the data. Select B2 and double-click the small square at the lower-right corner of the cell, called the fill handle. Excel usually fills the formula down alongside adjacent data. You can also drag the handle to the last row. To fill a selected range on Windows, select B2 and the cells below it, then press Ctrl+D. On Mac, use Command+D. In Excel for the web, the fill handle or the ribbon command can be used; keyboard behavior can depend on the browser and keyboard settings.
- Review the output column. Compare the cleaned values with their originals. Look for words that have joined together after a line break was removed, blanks that remain, or characters that still appear odd. CLEAN is intended to remove certain control characters; it is not a universal text-normalization tool. If the remaining issue is extra regular spaces, use TRIM as well. With the source text in A2 and the result in B2, enter
=TRIM(CLEAN(A2)). TRIM removes leading and trailing regular spaces and reduces repeated regular spaces between words to one. - Preserve word boundaries when removing line breaks. If a line break separates words and should become a space, replace that character before CLEAN removes it. With the source text in A2 and the result in B2, enter
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(10),CHAR(32)))). CHAR(10) represents a line-feed character, and CHAR(32) represents a regular space. For the example North, line break, wind, this formula returns North wind. It also removes other control characters that CLEAN recognizes and tidies repeated regular spaces. A carriage return or another unusual separator may need a separate replacement, depending on how the text was imported. - Handle nonbreaking spaces separately if needed. A nonbreaking space can look like an ordinary space but is not removed by CLEAN or treated like a regular space by TRIM. With the source text in A2 and the result in B2, use
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),CHAR(32))))to replace common nonbreaking spaces with regular spaces before cleaning. CHAR(160) represents the common nonbreaking space in many imported text sources. Check the output, because other invisible Unicode characters may require a different targeted replacement. - Choose whether to keep formulas or fixed text. If the cleaned results should update when the source changes, leave the formulas in place. If you need standalone text, select the cleaned cells, copy them, then use Home > Paste > Values to replace the formulas with their displayed results. Confirm the selection is correct before pasting, particularly if you plan to paste over the original column. In Excel for Mac or the web, the Paste Values option is also available through the Paste menu, though the menu layout may differ slightly.
Common Mistakes
- Expecting CLEAN to remove all spaces. CLEAN targets certain control characters, not regular spaces. Use TRIM when the issue is leading, trailing, or repeated regular spaces. If a nonbreaking space is involved, replace it explicitly, for example with SUBSTITUTE and CHAR(160).
- Assuming a removed line break becomes a space. CLEAN deletes the character. Text on either side can run together. If the break separates words, replace it with a space before cleaning, then inspect the result.
- Overwriting the source too soon. A helper column lets you compare original and cleaned text. Keep the source until you have confirmed that names, labels, and word boundaries still make sense.
- Expecting the result to be a number or date automatically. CLEAN returns cleaned text. A value that looks like a number or date may still be stored as text. Convert it separately only after checking the format and the intended result.
- Using CLEAN as a cure for every invisible character. It removes the specified ASCII control characters, not every Unicode formatting character or unusual whitespace character. A clean-looking result is not proof that every system will interpret it identically.
Tips
- Use a descriptive heading such as “Cleaned name” or “Cleaned description” for the result column. This helps prevent confusion when the source and output look nearly identical.
- Test the formula on a few representative rows before filling it down. Include a row with a known line break, a row with extra spaces, and a typical row with no obvious issue.
- If you are cleaning imported descriptions, consider whether line breaks are meaningful. They may separate paragraphs or address fields, so replacing every break with a space can remove useful formatting.
- For a large dataset, filter or sort the output alongside the original so you can inspect unusual changes. If the cleaned text feeds a lookup, comparison, or import, verify that the cleaned values work in that next step.
- Excel for Mac and Excel for the web support the CLEAN worksheet function. The formulas are the same as in Windows Excel; ribbon placement and keyboard shortcuts can differ. If a formula separator error appears, check your regional settings, since some Excel installations use semicolons instead of commas.
Troubleshooting
The formula shows an error. Confirm the cell reference exists and that the formula begins with an equals sign. The examples use commas between function arguments, as required in the standard Windows setup described here. Some regional configurations use semicolons; use the separator Excel expects in your installation. Also check that you have not accidentally included explanatory text or smart quotation marks in the formula.
The formula appears as text instead of calculating. The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, edit the formula (press F2, then Enter on Windows), and check that there is no apostrophe before the equals sign. On Mac, you can edit the cell and confirm the formula in the formula bar; the exact key for editing can depend on keyboard settings.
Nothing seems to change. The text may not contain any of the control characters CLEAN removes. It may instead contain repeated regular spaces, a nonbreaking space, or a Unicode character outside CLEAN’s range. Test TRIM for ordinary spaces, or use a targeted SUBSTITUTE for a known character. If you do not know which character is present, inspect the source system or try a small sample with the exact downstream task that is failing.
Two words have joined together. A line break or another separator was removed without leaving a replacement. If the source contains line feeds, use the SUBSTITUTE-and-CLEAN approach in the steps to turn line feeds into spaces first. Review the original data to confirm whether the separator is a line feed, a carriage return, or something else; a replacement aimed at the wrong character will not fix it.
Some imported records still fail to match. Compare the original and cleaned values, and check for nonbreaking spaces or other Unicode characters. CLEAN does not remove every invisible character, and TRIM only handles regular spaces. Replace a known character deliberately, then test the result in the lookup, comparison, or import process that exposed the issue. Avoid broad replacements until you understand which characters are present.
Frequently Asked Questions
What does CLEAN do in Excel?
CLEAN removes the first 32 nonprinting control characters in the 7-bit ASCII set from text. It is commonly used to tidy text copied or imported from other sources.
Does CLEAN remove spaces?
It does not remove ordinary spaces. Use TRIM to remove leading and trailing regular spaces and reduce repeated regular spaces between words. Nonbreaking spaces may need to be replaced separately.
Does CLEAN replace line breaks with spaces?
No. CLEAN removes recognized control characters, including common line-feed characters, rather than substituting a space. If a line break separates words, replace it with a regular space before applying CLEAN.
Can CLEAN remove nonbreaking spaces?
Not reliably by itself. A common nonbreaking space has character code 160, outside the 0–31 range CLEAN targets. You can replace it with a regular space using SUBSTITUTE and CHAR(160), then use CLEAN and TRIM if appropriate.
Does CLEAN change the original cell?
No. A formula such as =CLEAN(A2) displays the result in the cell containing the formula; it does not edit A2. To replace the original text, copy the reviewed results and paste values over the source, or keep the cleaned column separately.
Is CLEAN available in Excel for Mac and the web?
Yes. CLEAN is available in current Excel for Microsoft 365 on Windows, Mac, and the web. The worksheet formula works the same way, although menus and some shortcuts can differ by platform.
Related
- Excel Data Validation: Can You Select Multiple Items?
- Protect Sheet in Excel: Allow Editing Specific Cells
- Excel Conditional Formatting Icon Sets Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum Based on Filter: Formulas for Visible RowsTo sum only the rows currently visible after filtering in Excel, use SUBTOTAL. With values in B2:B100, enter…
- Excel Formula for First Day of Month: ExamplesIf the date you want to use is in cell A2, enter =DATE(YEAR(A2),MONTH(A2),1) to return the first day…
- COUNTIF Excel Formula Examples and How to Use ThemIn Excel, COUNTIF counts cells in a range that meet one condition. Its basic pattern is =COUNTIF(range,criteria); for…
- Conditional Formatting Dates in Excel: Rules and FormulasTo conditionally format dates in Excel for Microsoft 365 on Windows, select the date cells and choose Home…
- Power Query Lookup: How to Match Data Across TablesIn Excel, a Power Query lookup is usually a merge: open the Power Query Editor, choose Home >…
- How to Add Footnotes in a Word Table the Right WayTo add a true footnote to a Word table, click in the table cell where the reference belongs,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.