CLEAN function

CLEAN removes all non-printing characters (ASCII 0–31) from a text string, returning a clean version suitable for further processing or display.

=CLEAN(text)

Generate a CLEAN formula

Describe what you need. The generator will reach for CLEAN where CLEAN is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How CLEAN reads its arguments
textrequiredCLEAN →
ArgumentRequiredDescription
textRequiredThe text to clean; any non-printing characters (ASCII codes 0–31) are removed, while other characters are left unchanged. If the argument is an error value, that error is propagated.

Returns

CLEAN returns a text string of the same size as the input, with every non-printing character stripped out.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Strip hidden line-breaks from plan names

CustomerPlan
Acme CorpPro Plan Premium
=CLEAN(B2)

Result: Pro PlanPremium

Cell B2 contains a line-feed character (CHAR(10)) between "Pro Plan" and "Premium". CLEAN scans the string and removes the line-feed, concatenating the visible parts. The function returns the cleaned text without any hidden break.

2. Clean customer names that contain carriage returns

CustomerPlan
Beta LtdBeta Enterprise
=CLEAN(A3)

Result: Beta Ltd

The customer name in A3 includes a carriage-return (CHAR(13)) followed by a line-feed, which are both non-printing characters. CLEAN strips those characters, leaving only the readable name "Beta Ltd". No other characters are altered.

3. Prepare a combined MRR-date string for reporting

CustomerMRRSignup Date
Gamma Inc15002023-07-01
=CLEAN(C2&" "&D2)

Result: 1500 2023-07-01

The formula concatenates the MRR value with the signup date, inserting a space between them. If either cell contained hidden characters (for example, a trailing CHAR(13) from a data import), CLEAN would remove them, ensuring the combined string is safe for downstream analysis.

Common errors

Which CLEAN error are you seeing?
CLEAN returned an error#VALUE!
Correct the source formula that produces the error, or wrap the CLEAN call in IFERROR to supply a default text.
#NULL!
Reference a single cell or a proper range instead of an invalid intersection.
#N/A
Wrap the lookup in IFNA or IFERROR to provide a fallback string before applying CLEAN.
ErrorWhy it happensHow to fix it
#VALUE!The argument supplied to CLEAN is itself an error value such as #DIV/0! or #NUM!.Correct the source formula that produces the error, or wrap the CLEAN call in IFERROR to supply a default text.
#NULL!CLEAN is asked to operate on the intersection of two ranges that do not intersect, which yields a #NULL! error.Reference a single cell or a proper range instead of an invalid intersection.
#N/AA lookup function feeding CLEAN fails to find a match, returning #N/A, which CLEAN propagates unchanged.Wrap the lookup in IFNA or IFERROR to provide a fallback string before applying CLEAN.

Tips and when to use something else

  • Combine CLEAN with TRIM when you also need to strip leading or trailing spaces after removing hidden characters.
  • Use SUBSTITUTE if you must delete specific printable characters (such as commas or dashes) that CLEAN ignores.
  • TEXTJOIN works well with CLEAN to concatenate several cleaned fields into a single, tidy string for reporting.
  • When you need to replace characters rather than just remove them, prefer REPLACE or SUBSTITUTE instead of CLEAN.

Related functions

TRIMTRIM removes all leading and trailing spaces from a text string, returning a clean version without extra blanks.SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.RIGHTRIGHT returns the specified number of characters from the end of a text string, defaulting to one character if omitted.

Frequently asked questions

Why does CLEAN not remove ordinary spaces from my text?
CLEAN is designed only for non-printing control characters (ASCII 0–31). Regular space characters have ASCII code 32, which is considered printable, so CLEAN leaves them untouched. Use TRIM or SUBSTITUTE if you need to handle spaces.
Can CLEAN remove Unicode invisible characters like zero-width space?
No. CLEAN targets only the original ASCII control range. Unicode invisible characters have codes outside 0–31, so they survive a CLEAN call. To strip those, you would need SUBSTITUTE with the specific Unicode code or a custom LAMBDA.
What happens if I pass a number to CLEAN?
Excel and Google Sheets coerce the numeric value to text before cleaning, then return the same number as a text string because numbers contain no non-printing characters. The result is effectively the original number displayed as text.
How can I clean an entire column of imported data without writing a formula in each row?
Enter a single array formula such as =CLEAN(A2:A100) in a helper column (or use BYROW in newer versions) and press Enter. The formula will spill the cleaned results for the whole range, letting you replace the original column in one step.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator