In Excel, use =CLEAN(A2) to remove nonprinting ASCII control characters from the text in cell A2. CLEAN can remove things such as tabs and line breaks, but it does not remove ordinary spaces or every invisible Unicode character, so some imported text needs TRIM or SUBSTITUTE as well.
What the CLEAN function does
CLEAN is a text function that returns text with certain nonprinting characters removed. These characters may be difficult to see in a worksheet, but they can interfere with comparisons, sorting, lookups, or the appearance of a cell. They commonly arrive in data copied from websites, exported reports, or other systems.
Excel’s CLEAN function targets the first 32 nonprinting characters in the 7-bit ASCII character set, with character codes 0 through 31. This includes common control characters such as the horizontal tab, carriage return, and line feed. It removes those characters wherever they occur in the text; it does not replace them with a space.
For example, if a cell contains “North”, then a line break, then “East”, CLEAN joins the two visible parts as “NorthEast.” If a space is needed between them, add one deliberately with another function or correct the source data. CLEAN is not a general-purpose spelling, punctuation, or formatting repair tool.
CLEAN formula syntax and a basic example
The function takes one argument: the text to clean. That argument can be a cell reference, a text value, or the result of another formula. In the usual worksheet workflow, place the formula in a spare column so the original data remains available for checking.
With imported text in A2, enter this formula in B2:
=CLEAN(A2)
If A2 contains the visible words “North” and “East” separated by a line-feed character, B2 displays “NorthEast.” The formula returns a cleaned result; it does not edit A2 in place. Copy or fill the formula down to process additional rows, changing the reference for each row. In a Microsoft 365 workbook on Windows, the same function and formula syntax are used in Excel for Mac and Excel for the web.
To see the function’s effect with a known character, suppose A2 contains the text “Red”, followed by a tab character, followed by “Blue.” With that text in A2, use:
=CLEAN(A2)
The result is “RedBlue,” because the tab is removed rather than converted to a blank. The exact visual appearance of the original tab may vary by how the data was entered or imported, which is one reason a formula-based result is useful to inspect.
When CLEAN is useful
CLEAN is most useful when text looks normal but behaves oddly. A hidden line break or tab can make two strings compare as different, disrupt a one-line label, or leave unwanted breaks in a list. Cleaning a helper column can make it easier to standardize imported labels before you sort, filter, match, or summarize them.
- Imported names or descriptions: remove control characters copied from a report or text file before comparing records.
- Unexpected line breaks: turn multi-line imported text into a continuous string when that is appropriate for the task.
- Tabs inside fields: remove tab characters that came along with copied or exported content.
- Lookup preparation: clean a text key when you have confirmed that invisible control characters are causing a mismatch.
Do not assume every failed lookup is caused by a control character. Differences in spelling, punctuation, number-versus-text storage, ordinary spaces, and nonbreaking spaces can also matter. CLEAN is one diagnostic and cleanup step, not a guarantee that two values are equivalent.
Combine CLEAN with TRIM for spaces
CLEAN and TRIM solve different problems. CLEAN removes the targeted control characters. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to single spaces. If an imported value has both kinds of unwanted characters, combining the functions is often a practical starting point.
With the source text in A2, enter this in B2:
=TRIM(CLEAN(A2))
For example, if A2 contains two ordinary spaces before “Lake,” a tab after “Lake,” and several ordinary spaces before “View,” CLEAN removes the tab and TRIM tidies the ordinary spaces. The result is “Lake View.” If the tab was the only separator between the words, however, CLEAN removes it and may produce “LakeView”; CLEAN does not know that the tab was intended to act like a space.
Order and intent matter. If a particular control character should become a space rather than disappear, replace that character with a space before applying TRIM. This preserves a word boundary while still allowing repeated spaces to be tidied. Check a few real rows first: a formula can remove characters consistently while still producing text that is not what you intended.
Remove nonbreaking spaces and other invisible characters
A frequent source of confusion is the nonbreaking space, often represented by character code 160. It can look like an ordinary space but is not removed by CLEAN, and TRIM does not treat it as an ordinary space either. This is common in text copied from web pages. If you have confirmed that this character is present, SUBSTITUTE can replace it with an ordinary space.
With the source value in A2, use this formula in B2 to replace nonbreaking spaces with regular spaces:
=SUBSTITUTE(A2,CHAR(160)," ")
If you also want to remove ASCII control characters and normalize ordinary spaces, use this formula with the same A2 source:
=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))
That formula removes characters CLEAN recognizes, changes character 160 to a standard space, and then applies TRIM. Its result depends on the characters actually in the source. Other Unicode characters, including some invisible formatting marks, may remain because CLEAN is not designed to remove every character outside its ASCII control-character range. For those cases, identify the specific character and use a targeted replacement, or address the issue in the system that creates the data.
How to clean a column without losing the source
A helper column makes the operation reversible and easy to review. On Windows in Excel for Microsoft 365, a typical process is:
- Keep the original imported values in one column and add a heading such as “Cleaned” to an adjacent column.
- Enter the appropriate formula in the first data row, for example,
=CLEAN(A2)when the source is in A2. - Fill the formula down the rows that contain data. Review representative results, including unusual entries and values that previously failed to match.
- If the cleaned output is correct and you need fixed text rather than formulas, copy the results and use Paste Special to paste values over a destination column. Keep a copy of the original data until you have verified the outcome.
Excel for Mac and Excel for the web also support CLEAN. Ribbon labels or interaction details can vary by platform, but the formula itself is the same. If a column is part of a larger data-import process, consider fixing the source or import steps instead of repeating manual cleanup after every refresh. For a one-time cleanup, a formula helper column is transparent and simple to audit.
Common problems and limitations
The formula did not remove the visible gap. The gap may be an ordinary space, a nonbreaking space, or another Unicode character rather than one of the control characters CLEAN handles. Test a targeted replacement only after identifying the character; blindly removing spaces can run words together.
The cleaned words joined together. This is expected when the removed tab or line break was being used as a separator. Replace the known character with an ordinary space before applying TRIM if the words should remain separate. Do not add a space automatically if the character occurred within a code or identifier where joining is correct.
The result still does not match another value. Compare the two strings for punctuation, leading or trailing spaces, nonbreaking spaces, and differences in data type. CLEAN does not change capitalization, remove all punctuation, or convert numbers stored as text into numbers. A cleanup formula should address the confirmed cause, not just the symptom.
A line break disappears from a cell. That is the intended effect of CLEAN for the applicable control character. If line breaks are meaningful, do not use CLEAN on that content, or replace the relevant break with a delimiter that suits the task. Keep the original column until you know which information should be preserved.
The formula shows an error. Check that the cell reference is valid and that the source cell does not already contain an error value. CLEAN does not repair errors produced by other formulas. Also confirm that the formula begins with an equals sign and uses standard Excel parentheses.
CLEAN compared with other cleanup options
Choose a cleanup function according to the character or problem you need to address. Combining functions is reasonable when each step has a clear purpose, but a longer formula is not automatically a better one.
- CLEAN: removes the ASCII control characters in its supported range. It does not tidy ordinary spaces.
- TRIM: removes excess ordinary spaces at the start and end and reduces repeated ordinary spaces between words. It does not remove every invisible character.
- SUBSTITUTE: replaces a chosen piece of text or character with another value, making it useful for a known nonbreaking space or a specific unwanted mark.
- Find and Replace: can be suitable for a small, controlled one-time change, but formulas are easier to review and repeat across a changing dataset.
For recurring imports, the best option may be upstream cleanup: correct the export settings, source field, or data-transfer process so unwanted characters are not introduced. For a one-off worksheet, a helper formula is usually easier to inspect than editing the only copy of the source values.
Frequently Asked Questions
What is the CLEAN formula in Excel?
Use =CLEAN(A2) when the text to clean is in A2. It returns that text with supported nonprinting ASCII control characters removed.
Does CLEAN remove spaces in Excel?
No. CLEAN does not remove ordinary spaces. Use TRIM to tidy ordinary spaces, or SUBSTITUTE to replace a specific character such as a nonbreaking space.
Does CLEAN remove line breaks?
It removes the relevant ASCII line-break control characters from text. Because CLEAN removes them rather than replacing them, text on either side can join together.
Why does CLEAN not remove every invisible character?
CLEAN targets a defined set of ASCII control characters, not every Unicode character that may be invisible or look like a space. Identify the unwanted character and use a suitable targeted replacement when needed.
Can CLEAN change the original cell?
No. A formula such as =CLEAN(A2) returns a result in the cell where you enter it; A2 remains unchanged. To replace source values, review the results first and then paste values into the intended destination.
Related
- Can You Lock One Tab in Excel? Sheet Protection Explained
- Can You Lock Zoom in Excel? Limits and Workarounds
- Cell Description in Excel: Meaning, Contents, and Notes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Budget Tracking: Simple SetupThe core Excel formula for budget tracking is =SUM(income range)-SUM(expense range) for a net total, or =SUMIF(range,"category",sum_range) to…
- Excel Formula for Tracking Hours in a Daily TimesheetFor a basic Excel timesheet, put a start time in B2 and an end time in C2, then…
- Excel Formula for Current Date: TODAY ExplainedIn Microsoft Excel for Microsoft 365 on Windows, enter =TODAY() in a cell to display the current date.…
- Find Years of Service in Excel with a Simple FormulaTo calculate completed years of service in Excel, put each employee’s hire date in B2 and enter =DATEDIF(B2,TODAY(),"y")…
- Lowercase Names in Excel: Formula, Fill Down, and ReplaceTo lowercase names in Excel, use the LOWER function in a helper column, then fill the formula down.…
- Show Errors as Blank in Excel: Formula and OptionsYes. In Excel for Microsoft 365, wrap a formula in IFERROR and return an empty text string when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.