If the text you want to clean is in A2, enter =CLEAN(A2) in another cell. For example, if A2 contains North, a line break, and Wing, the result is NorthWing because CLEAN removes the line break rather than replacing it with a space.
What the CLEAN function does
Excel’s CLEAN function removes nonprinting characters from text. It is mainly useful when data comes from another system, a web page, a text file, or an older application and contains control characters that are not obvious on the worksheet. Those characters can interfere with searching, matching, sorting, or displaying text as expected.
CLEAN targets the first 32 nonprinting characters in the 7-bit ASCII character set, with codes 0 through 31. Examples include line-feed and carriage-return characters often used in imported text. The function returns text with those characters removed; it does not edit the original cell. That makes it possible to compare the cleaned result with the source before replacing anything.
CLEAN is not a general-purpose removal tool for every invisible character. Some Unicode characters, including the nonbreaking space commonly represented by character code 160, are outside the set it removes. That limitation is a frequent reason a value can look clean but still fail to match another value.
A basic CLEAN formula example
Suppose A2 contains the word North, a line break, and then the word Wing. With that content in A2, enter this formula in B2:
With the line-break text in A2, the formula returns NorthWing because the control character is deleted and no replacement is inserted: =CLEAN(A2)
The formula takes one argument: the text to clean. That argument can be a cell reference, a text value, or another formula that returns text. In ordinary worksheet use, a cell reference is convenient because the result updates when the source value changes.
To apply the cleanup to more rows, place the formula beside the first source value and fill it down using the fill handle. In Microsoft 365 for Windows, you can also select the formula cell and press Ctrl+D to fill down through selected cells. On Mac, the equivalent shortcut is Control+D. Check the resulting values before copying them over the originals.
Keep words apart when removing line breaks
Deleting a line break can join the text on either side. For example, CLEAN changes North, line break, Wing into NorthWing. If you want the words separated, replace the line break with a regular space before cleaning. With the line-break text in A2, use:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(10)," ")))
If A2 contains North, a line feed, and Wing, this returns North Wing. CHAR(10) represents the line-feed character, and SUBSTITUTE changes it to a space. CLEAN then removes any other supported control characters, while TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one.
Line breaks in imported text may use a carriage return as well as a line feed. CLEAN removes those supported control characters, but the order of operations matters if you want a space in their place: replace the relevant break character with a space before CLEAN removes it. Review a few source rows first, since not every control character represents a word boundary. In an address or a multi-line note, replacing a break with a space may be appropriate; in other data, removing it may be the desired result.
CLEAN versus TRIM
CLEAN and TRIM solve different problems. CLEAN removes certain nonprinting control characters. TRIM handles ordinary spaces, specifically leading and trailing spaces and repeated spaces between words. Neither function automatically performs the other’s task.
With ordinary spaces and control characters mixed in A2, this formula cleans both types of issue: =TRIM(CLEAN(A2)). The assumed layout is the unprocessed text in A2; the result is the cleaned text with the control characters removed and ordinary spaces normalized. Use it when you want to retain a single space between words.
TRIM may alter spacing that is meaningful in a particular field, so do not apply it blindly to fixed-width data or text where repeated spaces have a specific purpose. CLEAN alone is a more limited change. If the goal is only to remove control characters, start with CLEAN and inspect its output before adding TRIM.
Handling nonbreaking spaces and imported text
A common problem is a value that looks identical to another value but does not match in a lookup or comparison. One possible cause is a nonbreaking space, which behaves differently from the ordinary space character. CLEAN does not remove it, and TRIM does not reliably treat it as an ordinary space.
When A2 contains text that may include nonbreaking spaces, replace character 160 with a normal space, then clean and trim the result: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). If A2 contains a name with a nonbreaking space between the first and last names, this formula returns the name with a normal single space between them. The formula assumes the unwanted character is CHAR(160); it is not a universal fix for every invisible Unicode character.
Imported material may contain several kinds of unusual characters at once. A practical approach is to test the formula on a copy of the data, compare before-and-after values, and verify that important words, punctuation, and spacing remain intact. If a stubborn character remains, identify its actual character or code rather than repeatedly layering cleanup functions. Excel’s CLEAN and TRIM address common cases, not every possible encoding issue.
How to use CLEAN safely in a worksheet
- Keep the original imported column unchanged, at least until the cleaned output has been checked.
- Enter a formula in a nearby empty column, referring to the source cell in the same row.
- Inspect records that include line breaks, names, addresses, or unusual spacing. Confirm that cleaning did not join words that should stay separate.
- Fill the formula down the data and check a sample from the top, middle, and bottom of the list.
- If you need fixed text rather than formulas, copy the results and use Home > Paste > Paste Values to place the results as values in a suitable destination.
Formula results recalculate when their source values change, so retaining formulas can be useful when the source is updated regularly. If the cleaned column is going to be exported or used as a static result, pasted values can make the output independent of the original formulas. Keep a copy of the source data if the original characters might later be needed for troubleshooting.
Common problems and alternatives
If CLEAN appears to do nothing, the cell may not contain one of the control characters it removes. The issue could instead be a nonbreaking space, another Unicode character, or a difference in punctuation or spelling. If words run together after cleanup, that is usually because CLEAN deleted a line break without inserting a separator. Use SUBSTITUTE to change a known line break to a space before cleaning, as shown above.
For repeated import-cleaning work, Power Query can provide a repeatable transformation. In Excel for Microsoft 365 on Windows, select the source data and choose Data > From Table/Range to open the Power Query Editor. Select the relevant text column, then use Transform > Format > Clean. The transformation is applied when the query is loaded or refreshed, rather than being entered as a worksheet formula. Menu details can vary somewhat by platform and update; Excel for the web does not provide the full Power Query editing experience available in the Windows desktop app.
Use a worksheet formula when you want a visible, row-by-row result that updates with its source. Consider Power Query when the same cleanup needs to be repeated during a data-import process. Neither choice guarantees removal of every invisible Unicode character, so validate the output against the actual source and intended use.
Frequently Asked Questions
What is an example of CLEAN in Excel?
With text in A2, =CLEAN(A2) returns the text without supported nonprinting ASCII control characters. If A2 contains North, a line break, and Wing, the result is NorthWing.
Does CLEAN remove spaces in Excel?
CLEAN does not remove ordinary spaces. To remove extra ordinary spaces as well as supported control characters, use TRIM with CLEAN. Remember that TRIM normalizes repeated spaces, so it may change spacing you intended to keep.
Does CLEAN remove line breaks?
Yes, supported line-feed and carriage-return control characters are removed. CLEAN does not replace them with spaces. If a break separates words, substitute a space for the relevant break character before using CLEAN.
Why does CLEAN not remove a hidden character?
The character may not be one of the first 32 ASCII control characters handled by CLEAN. A nonbreaking space is a common example. You may need to replace that known character explicitly, or investigate the source encoding if the unwanted character is different.
Can I use CLEAN on a whole column?
Enter a formula referring to the first source cell, then fill it down for the rows you need. For recurring imports, Power Query’s Clean transformation may be more convenient. In either case, keep or copy the original data until you have checked the results.
Related
- Customer Details in Excel: How to Set Up a Useful List
- Customizable Calendar Template in Excel: How to Make One
- Email Merge from Excel: Steps, Setup, and Safer Sending
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Range Contains Text: Formula GuideYes, you can use COUNTIF to count cells that contain text, but the exact formula depends on what…
- Excel Wildcard XLOOKUP: Find Partial MatchesYes. In Microsoft Excel for Microsoft 365, XLOOKUP can find a partial text match when you set its…
- Excel Custom Number Format: Round Values to MillionsYes. In Excel for Microsoft 365 on Windows, apply the custom number format #,##0,,"M" to display numbers in…
- Excel Formula for Finding Matches: A Practical GuideTo find the position of a value, use MATCH with exact-match mode. With the value to find in…
- Excel Formula for Highest Value: MAX and Other OptionsTo find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare…
- Microsoft Excel Concatenate Text: Formulas and ExamplesTo concatenate text in Microsoft Excel, join cell contents with the ampersand operator (&), or use CONCAT or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.