In Excel, use CLEAN to remove certain nonprinting control characters from text, and use TRIM to remove ordinary extra spaces: TRIM removes spaces at the beginning and end and reduces repeated spaces between words to one. They solve different problems, so imported text may need both.
The core difference between CLEAN and TRIM
CLEAN and TRIM are text functions, but they target different characters. CLEAN removes certain control characters that are not normally visible, such as tabs or line breaks embedded in a cell. TRIM targets the standard space character: it strips spaces before and after text and compresses multiple spaces within the text into a single space.
For example, a cell might look like it contains the name Alex Morgan but actually have several spaces between the names. TRIM can normalize those spaces. Another cell might contain a tab or a line break copied from a web page. CLEAN can remove those control characters. A cell can have both problems at once, which is why combining the functions is common.
Neither function is a general-purpose remover for every invisible or unusual character. CLEAN does not remove ordinary spaces, and TRIM does not remove tabs, line breaks, or every Unicode whitespace character. Identifying which character is causing the problem is the key to choosing the right fix.
How CLEAN works
In Microsoft 365 for Windows, CLEAN removes the first 32 nonprinting ASCII characters, with character codes 0 through 31. These include control characters that can appear in text copied from other systems. Tabs and line breaks are familiar examples. They may not be visible in the cell, but they can affect matching, sorting, or how text appears.
With the value to clean in A2, use =CLEAN(A2). If A2 contains the text North, then a line break, then West, the result is NorthWest because CLEAN deletes the line-break character rather than replacing it with a space. That distinction matters: when a removed character separates two words, deleting it can join the words together.
If you want to preserve a word boundary, replace the specific character with a space instead of deleting it. With the original text in A2 and the unwanted line break represented by CHAR(10), use =SUBSTITUTE(A2,CHAR(10),CHAR(32)). This changes line breaks into ordinary spaces; if there are repeated spaces afterward, TRIM can normalize them. CHAR(10) represents a line feed, while CHAR(32) represents a standard space.
CLEAN can help with some pasted or imported text, but its scope is limited to those ASCII control codes. It does not remove every character that might look blank, and it does not repair spelling, punctuation, or inconsistent capitalization.
How TRIM works
TRIM removes standard spaces at the start and end of text and changes each run of two or more standard spaces inside the text to one. With the text in A2, use =TRIM(A2). If A2 contains three leading spaces, the words Alex and Morgan separated by four spaces, and two trailing spaces, the result is Alex Morgan with one space between the names.
That internal-space behavior is useful for names, addresses, product descriptions, and other fields where repeated spaces are usually accidental. It is also different from simply deleting all spaces: TRIM keeps one space between words, so the result remains readable.
TRIM does not remove tabs or line breaks, even though they may look like spacing. It also does not reliably remove nonbreaking spaces, a different character often found in text copied from websites. A cell can appear to have ordinary spaces but still fail an exact match if it contains one of these other characters.
When to use each function
Choose the function based on the unwanted character, not just on the fact that a lookup or comparison is failing. Use CLEAN when text contains nonprinting ASCII control characters. Use TRIM when it contains unwanted standard spaces. Use both when the source may contain both kinds of problems.
- Use TRIM for extra spaces in names, labels, or descriptions.
- Use CLEAN for embedded tabs or line breaks that should be removed.
- Use SUBSTITUTE when a particular character should be replaced rather than deleted.
- Use a combination when imported text has control characters and irregular spacing.
With text in A2, the combined formula is =TRIM(CLEAN(A2)). It first removes the control characters CLEAN recognizes, then normalizes standard spaces with TRIM. For instance, if the text contains a tab before Alex and multiple spaces between Alex and Morgan, the result is Alex Morgan. But if the removed tab was the only separator between two words, CLEAN may join them; inspect the result before using a combined formula on an entire column.
Cleaning imported text without losing meaning
Text copied from a website, exported from a database, or brought in from another application may contain more than one kind of invisible character. A sensible workflow is to preserve the source column, create a cleaned column beside it, and compare several results before replacing anything. This makes it easier to catch cases where removing a character changes the meaning or joins separate words.
- Keep an unchanged copy of the original data, especially when the source is important or difficult to retrieve.
- Test a formula on a few representative rows, including rows with visible spacing problems and rows that appear normal.
- Check whether words have been joined, punctuation has shifted, or identifiers have changed.
- Fill the formula down the column and review the output before using it for matching, sorting, or reporting.
- If you need fixed results rather than formulas, copy the cleaned cells and use Paste Special > Values in the destination.
For recurring imports, Power Query is another option in Excel for Microsoft 365 on Windows. In the Power Query Editor, select a text column and look under Transform > Format for text-cleaning options such as Trim and Clean. The query records the transformation so it can be applied again when refreshed. Review the preview: deleting control characters may join text just as it can in a worksheet formula.
Nonbreaking spaces and other stubborn characters
A common reason TRIM appears not to work is a nonbreaking space. It is often represented by character code 160 and can be inserted when text is copied from a web page or formatted document. It looks much like a regular space, but TRIM does not treat it as the standard space it removes. CLEAN does not remove it either, because it is outside CLEAN’s stated range of ASCII codes 0 through 31.
With text in A2, this formula replaces character 160 with an ordinary space, removes recognized control characters, and then trims the result: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),CHAR(32)))). If A2 contains Alex, a nonbreaking space, and Morgan, the result is Alex Morgan with one ordinary space between the names. This works because SUBSTITUTE targets the specific character before TRIM handles the replacement space.
There are other Unicode characters that can resemble spaces or marks. If a formula does not solve the problem, do not assume the cell is clean just because it looks right. Try inspecting the source, testing a specific character replacement, or reimporting the data with a controlled transformation. A formula that removes every unusual character indiscriminately can damage legitimate text.
Common problems and safe checks
Cleaning text can affect more than appearance. An exact-match lookup may fail because of an unseen character, but removing a character can also make two values identical when they should remain distinct. For example, a line break may separate parts of a label, and deleting it can combine them. Examine representative results and preserve the original data until you have confirmed the cleaned column is fit for its purpose.
Be particularly cautious with account numbers, product codes, postal codes, and other identifiers. Keep them as text when leading zeros or exact character sequences matter, and compare the cleaned output with the source. CLEAN and TRIM are intended for text cleanup; they are not tools for validating an identifier or deciding which characters are meaningful.
Also remember that a formula result updates if its source changes. If you need a permanent cleaned copy, copy the results and paste values. In Excel for Windows, the Paste Special command is available from the Home tab under Paste; the Mac interface also provides Paste Special, though the ribbon layout can differ. Save a copy of important source data before making bulk changes.
Frequently Asked Questions
Should I use CLEAN or TRIM first? If you use both, CLEAN is commonly placed inside TRIM, as in =TRIM(CLEAN(A2)). CLEAN removes recognized control characters first; TRIM then handles ordinary spaces. Check whether a deleted character was acting as a word separator.
Does TRIM remove spaces between words? It reduces repeated standard spaces between words to one; it does not remove the single separating space. It also removes standard spaces at the beginning and end of the text.
Does CLEAN remove line breaks in Excel? CLEAN removes line-feed and other control characters in its supported ASCII range. If you need the line break to become a space rather than disappear, replace it with SUBSTITUTE and CHAR(32), then use TRIM if needed.
Why does TRIM not remove a space copied from a website? The character may be a nonbreaking space rather than an ordinary space. Replace character 160 with a standard space using SUBSTITUTE, then apply TRIM to normalize the result.
Can I use CLEAN and TRIM on a whole column? Yes. Put a formula in a helper column beside the first row of text and fill it down. Review the results, then copy and paste values if you need a fixed cleaned column. Keep the original column until you have checked the output.
Related
- Excel Named Range Scope: Workbook vs. Worksheet Names
- Excel Named Range Manager: How to View and Edit Names
- Excel Named Range Greyed Out? Causes and Fixes to Try
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Between Two Dates: Steps and Common FixesTo filter Excel dates between two limits, open the filter arrow in the date column, choose Date Filters…
- Excel Formula Greyed Out? Causes and FixesIf Excel’s formula commands are greyed out, first press Esc to leave cell-edit mode, then select a worksheet…
- Sort Excel Data by Date: Steps and TroubleshootingTo sort Excel data by date, select the entire table or the date column within it, then choose…
- Excel Formula List A to Z: Functions and Examples GuideAn Excel formula is an expression that begins with an equals sign; most formulas use functions such as…
- Excel UNIQUE Sort: List Distinct Values in OrderTo sort unique values in Excel, combine the UNIQUE and SORT functions: if the source values are in…
- How to Copy a Formula Across an Entire Row in ExcelTo copy a formula across a row in Excel, enter it in the leftmost cell, select that cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.