In Excel for Microsoft 365, use =TRIM(CLEAN(A2)) to remove many nonprinting control characters from a cell and trim extra ordinary spaces. This assumes the text to clean is in A2; for example, a value with leading, trailing, or repeated spaces becomes a neatly spaced version in the formula cell. The original cell stays unchanged.
What CLEAN and TRIM Do
CLEAN and TRIM address different kinds of unwanted characters. CLEAN removes certain control characters that can arrive when text is copied from other software, imported from a file, or downloaded from a website. These characters are not always visible, but they may interfere with comparisons, sorting, or other formulas.
TRIM removes ordinary space characters (character code 32) at the beginning and end of text and reduces repeated ordinary spaces between words to one space. For example, it can turn a name with several spaces between the first and last name into a name with one separating space. It does not remove every kind of whitespace, and it does not remove spaces that separate words altogether.
Putting the functions together is useful because cleaning control characters alone does not standardize ordinary spaces, while trimming alone does not remove the control characters that CLEAN recognizes. In the combined formula, CLEAN processes the cell contents first, and TRIM then tidies the ordinary spaces left in the result.
How to Use the Formula
Suppose the uncleaned text is in A2 and you want the result in B2. Enter this formula in B2:
With the source text in A2, the formula for B2 is =TRIM(CLEAN(A2)). If A2 contains Ada Lovelace with two spaces before Ada, three between the names, and one after Lovelace, B2 displays Ada Lovelace.
- Place the source text in one column, such as A2:A100.
- In the adjacent column, enter the formula for the first row, adjusting the reference if your data starts elsewhere.
- Fill the formula down the rows that contain source text. Excel updates the relative reference for each row.
- Check several results, especially records that were copied or imported from different sources.
- If you need permanent cleaned text rather than formulas, copy the results and paste them as values into the intended destination.
The formula returns cleaned text in the cell containing the formula; it does not edit A2 in place. Keeping the raw input column until you have checked the results gives you a way to compare the cleaned output with the source and recover the original if necessary.
What the Formula Does to Different Text
With the source text in A2, =TRIM(CLEAN(A2)) can transform a value such as North Branch into North Branch. It removes the leading and trailing ordinary spaces and reduces the three ordinary spaces between the words to one.
If A2 contains a tab or a line-break character, CLEAN removes recognized control characters. That can be helpful when those characters are accidental, but it can also affect how text reads. For example, if a line break separates two words, removing the break may join them. CLEAN does not automatically replace every removed character with a space. Inspect data containing addresses, notes, or multi-line descriptions before applying the formula to a whole column.
Numbers and dates also deserve a check. CLEAN and TRIM are text functions. A number stored as text may remain text after cleaning, and a genuine numeric value may display in a way that depends on the cell format. If a later calculation requires numbers, verify the result’s type and convert it appropriately rather than assuming that a cleaned display is numeric.
Nonbreaking Spaces and Other Characters
A frequent reason the combined formula appears not to work is the nonbreaking space, often found in text copied from web pages. It looks like an ordinary space, but it is a different character. TRIM is designed to trim ordinary spaces, and CLEAN does not remove this character. The visible text may therefore appear unchanged even though the cell contains a nonbreaking space.
If you know the unwanted character is a nonbreaking space, replace it with an ordinary space before cleaning and trimming. With the source text in A2, use this formula in B2:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
SUBSTITUTE changes each character represented by CHAR(160) into a regular space. CLEAN then removes recognized control characters, and TRIM handles ordinary spaces at the beginning, end, and between words. For instance, if a web-copied phrase has a nonbreaking space between two words, the result retains a normal single space rather than joining the words.
There are other Unicode whitespace and invisible characters that this formula does not necessarily address, including some zero-width characters. Do not assume that one formula can identify and remove every invisible character in every language or data source. If this formula leaves a problem character behind, identify the specific character first, then add a targeted replacement or use a more specialized cleanup method.
Preserving Line Breaks and Intended Formatting
Cleaning is not always the right choice for every text column. A paragraph, mailing address, or product description may use line breaks intentionally. Because CLEAN removes certain control characters, the combined formula can remove those breaks rather than preserve the layout. It may also make adjacent text run together when a break was serving as a separator.
If the problem is unwanted line breaks and you want a space where each break occurred, replace carriage returns and line feeds with spaces before CLEAN and TRIM. With the source text in A2, enter this in B2:
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," ")))
This formula handles the common carriage-return and line-feed characters by turning them into ordinary spaces. TRIM then removes extra spaces at the edges and reduces repeated spaces between words. Check examples with multiple consecutive breaks, since they will become multiple spaces before TRIM reduces them. If the original line layout matters, keep it in a separate column and do not apply a cleanup formula that removes it.
Why the Result May Still Look Unchanged
First, check whether the problem is actually an ordinary space or one of the characters covered by the chosen formula. A nonbreaking space is the common exception, but imported data can contain other invisible characters. A cell may also contain a visible punctuation mark or a different Unicode character that CLEAN and TRIM are not intended to remove.
- Check the formula reference: Confirm that the formula points to the cell that contains the problem text, not a neighboring blank cell.
- Check the formula result: Select the result cell and look at its value in the formula bar. The displayed cell width or formatting can make two different values look alike.
- Check for nonbreaking spaces: Try the SUBSTITUTE and CHAR(160) version when the text came from a web page or report.
- Check for intentional characters: A hyphen, punctuation mark, or special symbol is not an extra space and will not be removed by these functions.
- Check the source type: A number stored as text can look like a number while behaving differently in calculations.
In some regional settings, Excel uses semicolons rather than commas between function arguments. If Excel rejects a formula written with commas, check the list separator used by your installation. The examples here use commas, as commonly shown in English-language Excel.
Formula Results, Filling Down, and Replacing Source Data
A formula-based cleanup is usually safest when you are working with a large or important source list. It leaves the source untouched, updates if the source changes, and makes it easy to compare before and after. After filling the formula down, scan for blanks, unexpected joined words, and records that still contain unusual spacing.
When the cleaned values are approved and you want to replace the formulas with their current results, copy the cleaned cells and use Excel’s Paste Values option in the destination. The pasted values no longer update when the source changes. Keep a copy of the original data if it may be needed later, especially when you are cleaning externally supplied records or preserving an audit trail.
For a small one-off correction, editing a cell manually may be quicker. For repeat imports or a recurring cleanup process, formulas make the transformation more consistent. If you use Power Query for a data-import workflow, it can also standardize text as part of the repeatable import steps; the formula method is often simpler when you only need to clean worksheet cells.
Common Misconceptions and Alternatives
The formula does not remove all spaces. TRIM deliberately retains one ordinary space between words, which is normally what you want for names and sentences. It also does not delete all invisible characters: CLEAN is limited to certain control characters, and nonbreaking spaces require separate handling. Nor does the formula change the original cell unless you replace the source with the result.
Use SUBSTITUTE when you know which character should be replaced, as in the nonbreaking-space example. Use the combined CLEAN and TRIM formula when you want to remove recognized control characters and normalize ordinary spacing. For a dataset with multiple kinds of imported characters, a single broad formula may not be enough; identify the characters that cause the issue and test a targeted cleanup on representative rows.
Finally, cleaning text is different from removing duplicate records. Two cells that look identical may still contain different invisible characters, so cleanup can help make later comparisons more reliable, but it does not itself find or delete duplicates. Review the cleaned results before using Excel’s duplicate-removal tools or relying on exact text matches.
Frequently Asked Questions
What is the CLEAN TRIM formula in Excel?
With the text in A2, use =TRIM(CLEAN(A2)). CLEAN removes recognized nonprinting control characters, and TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one.
Does TRIM remove nonbreaking spaces?
No. TRIM is designed for ordinary spaces, not nonbreaking spaces. If the unwanted character is a nonbreaking space, use SUBSTITUTE with CHAR(160) to replace it with a regular space, then apply CLEAN and TRIM.
Does CLEAN remove line breaks?
CLEAN removes certain control characters, including common line-break characters. That can make text easier to compare, but it may also remove intended formatting or join text that was separated by a line break.
Will this formula change the original cell?
No. The formula returns a cleaned result in the cell where you enter it. The source remains unchanged unless you deliberately copy the result and paste it over the source as a value.
Why does the formula leave some strange characters behind?
CLEAN handles a limited set of control characters, while TRIM handles ordinary spaces. Other Unicode characters, such as some nonbreaking or zero-width characters, may need to be identified and replaced separately.
Related
- Print an Excel Spreadsheet: Setup, Pages, and Tips
- Can You Password Protect an Excel Spreadsheet?
- Can You Print an Excel Sheet? Settings, Pages, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum Lookup: Sum Values That Match a CriterionTo add every value that matches a lookup criterion in Excel, use SUMIF. With product names in A2:A10,…
- Excel COUNTIF Does Not Contain Text: Formula GuideNo, Excel’s COUNTIF function does not have a built-in “does not contain” operator, but you can count cells…
- Excel COUNTIF Range Examples: Text, Numbers, and DatesUse Excel’s COUNTIF function to count cells in a range that match one condition: for example, =COUNTIF(A2:A20,"East") counts…
- Excel ROUNDUP Formula: 10 Ways to Use ItThis article lists 10 practical ways to use Excel’s ROUNDUP function, from basic rounding to combining it with…
- Excel Count Dates Older Than Today: Formula and FixesTo count dates in Excel that are earlier than today, use =COUNTIFS(A2:A100,"
- How to Add Filter Options in Excel: A Step-by-Step GuideTo add filter options in Excel for Microsoft 365 on Windows, select a cell in your data, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.