Text
CLEAN Function in Excel
Removes all non-printable characters from text.
Syntax
- =CLEAN(text)
Arguments
- text (required): Text to clean
Examples
- =CLEAN(A1) - Remove non-printable chars - Result: Cleaned text
CLEAN best practices
- Use CLEAN to remove non-printing characters from imported text.
- Combine CLEAN with TRIM for more reliable lookup, COUNTIF, and text comparison formulas.
Excel CLEAN function documentation
- CLEAN removes non-printable characters (ASCII 0–31) from text except tab, line feed, and carriage return.
- Syntax: =CLEAN(text). Essential after CSV imports and copy-paste from PDFs or web forms.
- Pair with [TRIM](/functions/trim/) for leading/trailing spaces — order: =TRIM(CLEAN(A2)).
- Related: [SUBSTITUTE](/functions/substitute/), [VALUE](/functions/value/), [VLOOKUP](/functions/vlookup/) fixes.
CLEAN syntax and when to use it
- text (required): cell or string to sanitize.
- CLEAN does not remove regular spaces — use TRIM after CLEAN.
- Does not convert text numbers to values — follow with VALUE or multiply by 1.
- Hidden chars often break [COUNTIF](/functions/countif/) and lookup exact match.
Step-by-step: fix VLOOKUP after import
- Step 1 — Lookup column shows #N/A though values look identical.
- Step 2 — Helper: =TRIM(CLEAN(A2)) on both lookup and table columns.
- Step 3 — Point VLOOKUP to cleaned helper or paste values over source.
- Step 4 — Confirm with =EXACT(old, CLEAN(old)) → FALSE when chars were hidden.
- Step 5 — Read [VLOOKUP returning #N/A](/problems/vlookup-returning-na/).
CLEAN vs TRIM vs Power Query
- CLEAN — non-printable control characters.
- TRIM — extra spaces between words and edges.
- Power Query — repeatable clean on refresh for large imports.
- Problem guide: [countif not counting text](/problems/countif-not-counting-text/).
Worked examples to copy
- Basic clean: =CLEAN(A2).
- Clean and trim: =TRIM(CLEAN(A2)).
- Clean then number: =VALUE(TRIM(CLEAN(A2))).
- Lookup on clean ID: =XLOOKUP(TRIM(CLEAN(E2)), clean_id_col, return_col).
People also ask
- CLEAN remove line breaks? — Keeps LF/CR; use SUBSTITUTE for custom removal.
- CLEAN fix #N/A in VLOOKUP? — Often yes when non-printing chars differ.
- CLEAN on numbers? — Coerces to text first; may return text result.
- CLEAN vs CODE function audit? — Use CODE on LEFT char to detect hidden bytes.
Common errors
- May not remove all special Unicode characters
Use cases
- Data cleaning
- Import cleanup
- Text sanitization
Frequently asked questions
- What characters does CLEAN remove? CLEAN removes ASCII characters 0-31 (non-printable control characters) including null, tab, line feed, carriage return. It does NOT remove non-breaking spaces (CHAR(160)) or other Unicode special characters.
- How do I remove non-breaking spaces that CLEAN doesn't catch? Use =SUBSTITUTE(CLEAN(A1), CHAR(160), " ") to replace non-breaking spaces with regular spaces, then TRIM to clean up. Or =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) for complete cleanup.
- Why is my imported data still showing weird characters after CLEAN? CLEAN only removes ASCII 0-31. For other problematic characters, identify them with CODE() or UNICODE(), then use SUBSTITUTE to remove. Common culprits: CHAR(160) non-breaking space, CHAR(127) delete, various Unicode spaces.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use CLEAN
- Data cleaning — common Text scenario for CLEAN.
- Import cleanup — common Text scenario for CLEAN.
- Text sanitization — common Text scenario for CLEAN.
CLEAN in the Text category
- Browse all Text functions at /categories/text/ for related formulas.
- CLEAN syntax: =CLEAN(text)
- text (required): Text to clean
- Confirm CLEAN arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm CLEAN arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- TRIM (/functions/trim/): Removes extra spaces from text, leaving only single spaces between words.
- SUBSTITUTE (/functions/substitute/): Replaces occurrences of old text with new text in a string.
Common problems and fixes
- Why Is XLOOKUP Not Finding a Value? (/problems/xlookup-not-finding-value/): Check for extra spaces, mismatched text/number types, wrong lookup_array, and match_mode settings before adding if_not_found text.
- Why Is COUNTIF Not Counting Text? (/problems/countif-not-counting-text/): Clean the source data with TRIM and CLEAN, then use exact text criteria or wildcards such as "*text*".
Errors to watch for
- May not remove all special Unicode characters — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter CLEAN on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
CLEAN worked examples to copy
- =CLEAN(A1) — Remove non-printable chars. Expected result: Cleaned text.
CLEAN reference summary for crawlers and offline review
- CLEAN belongs to the Text category in Excel. Removes all non-printable characters from text.
- Full syntax: =CLEAN(text). Open /functions/clean/ for parameters, FAQs, and related pages.
- Common mistakes: May not remove all special Unicode characters
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.