In Excel, CLEAN removes many nonprinting control characters, while TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. To apply both to text in A2, enter =TRIM(CLEAN(A2)) in another cell; the formula returns a cleaned result without changing the original cell.
What CLEAN and TRIM do
CLEAN and TRIM are text functions often used to fix data imported from websites, reports, databases, or other applications. A value can look normal on screen but contain characters that interfere with sorting, matching, counting, or combining text. These functions address two different kinds of mess: CLEAN targets certain control characters, and TRIM standardizes ordinary spaces.
The names can be misleading. CLEAN does not remove every invisible Unicode character, and TRIM does not eliminate every kind of whitespace. They are useful first steps, not a guarantee that all text is standardized. The right formula depends on what is actually in the cell and where the data came from.
- Use CLEAN when text may contain line breaks, tabs, or other low-level control characters.
- Use TRIM when words have unintended leading, trailing, or repeated ordinary spaces.
- Combine them when both problems may be present.
How the CLEAN function works
CLEAN(text) removes the first 32 nonprinting characters in the ASCII character set, with codes 0 through 31. In practical spreadsheets, this can include tab characters, line feeds, and carriage returns that arrive with copied or imported text. The function returns the remaining text, with those characters removed.
For example, a copied address may contain a line break between the street and city. CLEAN can remove that line break, but it does not decide to replace it with punctuation or a space. If the break separates words, the words may run together after removal. Inspect the result and use a deliberate replacement if a separator should remain.
CLEAN is not a formatting command. It does not change font, cell color, number formatting, or the underlying layout of a worksheet. It also does not remove all characters that are invisible or unusual in modern text. Unicode characters outside that original 0–31 range can remain, including some characters copied from web pages.
How the TRIM function works
TRIM(text) removes ordinary space characters at the beginning and end of text and reduces runs of ordinary spaces inside the text to a single space. For instance, a name displayed as “ Maya Chen ” becomes “Maya Chen.” This makes TRIM useful for cleaning inconsistent spacing in names, labels, and descriptions.
TRIM does not remove every character that a person might think of as a space. Most importantly, it does not remove the nonbreaking space, character code 160, commonly found in text copied from web pages. A cell containing that character can look as if it has an ordinary space while still failing an exact match or remaining unchanged by TRIM.
TRIM also does not correct spelling, capitalization, punctuation, or spaces that are meaningful parts of a value. If a product code uses internal spaces intentionally, collapsing them could change the identifier. Keep a copy of source data and check the expected format before applying cleanup across a large list.
Combining CLEAN and TRIM
For a basic cleanup, put the source text in A2 and enter the formula below in B2. Fill the formula down to process other rows. CLEAN removes supported control characters first; TRIM then removes extra ordinary spaces that remain.
With the original text in A2, =TRIM(CLEAN(A2)) returns the cleaned text in the cell containing the formula. For example, if A2 contains two leading spaces, three spaces between “North” and “Region,” and two trailing spaces, the result is “North Region.” The formula does not overwrite A2.
The order is useful because removing control characters can leave ordinary spaces at the beginning or end, or multiple spaces next to each other. TRIM handles those ordinary spaces after CLEAN has done its work. However, if a removed tab or line break was acting as a word separator, CLEAN can join the words. In that case, replace the specific character with a normal space before trimming rather than removing it outright.
Cleaning nonbreaking spaces
A frequent reason the combined formula seems not to work is a nonbreaking space. It often appears in copied web text and looks much like a regular blank. You can convert that character to an ordinary space with SUBSTITUTE, then run CLEAN and TRIM. Replacing it with a space rather than deleting it preserves separation between words.
Assuming the copied text is in A2, use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). CHAR(160) identifies the nonbreaking space; SUBSTITUTE replaces each matching instance with a regular space. CLEAN and TRIM then handle the supported control characters and ordinary spacing. If the text contains no character 160, the formula still returns a cleaned version of the text.
This formula addresses a common case, not every possible invisible character. Some imported text can contain other Unicode spacing or formatting characters. If one troublesome character remains, identify it from the source or inspect the text with suitable tools, then replace that specific character. Avoid deleting all unfamiliar characters blindly: some may be meaningful marks in names or other languages.
Using the formulas in a worksheet
In Excel for Microsoft 365 on Windows, enter the formula in a blank helper column beside the original data. For a list beginning in row 2, use the relevant formula in the first result row and copy it down with the fill handle or by copying and pasting. Check several results, including rows with punctuation, accented letters, and intentional spaces, before relying on the cleaned column.
When the results are correct and you need to replace the original values, copy the result cells and use Home > Paste > Values to paste the calculated text as fixed values. Only then, if appropriate, remove the original column. Keep a backup if the source records matter, because a formula-based cleanup is easier to inspect and reverse than overwriting the only copy.
The same CLEAN, TRIM, SUBSTITUTE, and CHAR formulas work in current Excel for Mac and Excel for the web. The ribbon layout can differ slightly by platform, but formula behavior is the same. If Excel rejects a comma-separated formula, check your regional list-separator settings; some installations expect semicolons between arguments.
Common problems and limits
- The formula changes nothing: The issue may be a nonbreaking space or another Unicode character that CLEAN and TRIM do not handle. Try the CHAR(160) replacement when the text came from a web page.
- Words run together: CLEAN may have removed a tab or line break that separated them. Replace that particular character with an ordinary space instead.
- Matching still fails: Check for punctuation differences, different spellings, hidden Unicode marks, or inconsistent capitalization. CLEAN and TRIM do not standardize those features.
- The result looks different from the source: A formula returns text in a new cell; it does not preserve every visual detail or formatting choice from the original cell.
These functions are also not substitutes for checking data types. A value that looks like a number may be stored as text, and cleaning spaces alone does not necessarily convert it into a numeric value. Likewise, a cleaned result may still need separate validation before it is used for lookups, reports, or imported records.
When to use a different approach
For a one-time cleanup of a small list, helper-column formulas are transparent and easy to review. If the same cleanup is repeated whenever a file is refreshed, Power Query may be more suitable: its transformation steps can be saved and rerun during a refresh. Choose transformations carefully and verify the output, since a general trim or clean operation may not address every unusual character in a source file.
For exact-match problems, first compare the raw text and determine what differs rather than adding more cleanup blindly. The common nonbreaking-space formula solves one frequent mismatch, but it cannot infer whether two different-looking codes should be treated as equivalent. Keep the original data until you have confirmed that the cleaned values meet the rules for your task.
Frequently Asked Questions
What is the difference between CLEAN and TRIM in Excel?
CLEAN removes certain nonprinting control characters with ASCII codes 0 through 31. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. They address different problems.
Does TRIM remove all extra spaces?
No. It handles ordinary space characters, but not nonbreaking spaces such as character 160. If copied web text is unaffected, replace CHAR(160) with an ordinary space and then apply TRIM.
Can CLEAN and TRIM change the original cell?
No. A formula such as =TRIM(CLEAN(A2)) returns a result in the cell where you enter it and leaves A2 unchanged. To replace source values, copy the results and paste values after checking them.
Why does CLEAN make two words run together?
CLEAN removes a control character such as a tab or line break; it does not replace it with a space. If that character separates words, substitute an ordinary space for it before trimming.
Do CLEAN and TRIM work in Excel for Mac or the web?
Yes. These formulas are supported in current Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. Ribbon placement may vary, but the functions work the same way.
Related
- How to Ungroup in Excel: Sheets, Rows, and Columns
- Superscript in Excel: How to Format Text and Numbers
- Share an Excel Link: Steps, Access, and Safety
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Joining Two Text Columns: Formulas and ExamplesTo join two text columns in Excel, enter a formula such as =A2&" "&B2 in a third column,…
- Excel Days Until Date: Formulas, Examples, and FixesTo calculate calendar days until a date in Excel, subtract today’s date from the target date: with the…
- Checklist Examples in Excel: Templates and FormulasUseful checklist examples in Excel include task lists, inspections, inventory counts, event plans, and habit trackers. For a…
- EDATE in Excel: Add Months, Not DaysEDATE in Excel moves a date by whole calendar months; it does not add a specified number of…
- Excel SUM and AVERAGE: Formulas, Examples, and TipsIn Excel, use SUM to add numbers and AVERAGE to find their arithmetic mean. With values in cells…
- Split Cells in Excel With Formulas: A Step-by-Step GuideTo split text from one Excel cell into separate columns, enter =TEXTSPLIT(A2," ") in B2 when A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.