In Excel for Microsoft 365 on Windows, use TRIM to remove ordinary spaces at the beginning and end of text and reduce repeated spaces between words to one. If imported text still looks misaligned afterward, it may contain nonbreaking spaces or other characters that TRIM does not remove; the formulas and checks below help identify those cases.
What TRIM does to Excel text
TRIM is a text-cleanup function for the standard space character, ASCII character 32. It removes spaces before the first character and after the last character, then reduces each run of two or more ordinary spaces inside the text to one. It does not remove the spaces that separate words completely.
For example, if A2 contains the text with three spaces before and after it and several spaces between the words, TRIM returns North Harbor, with one space between the words and none at either end. The function changes the text result; it does not alter the original cell unless you replace the source data with the result.
TRIM is useful when spaces make otherwise identical entries behave differently. A name with a trailing space may fail an exact match against the same name without that space. Extra internal spaces can also make labels look inconsistent or interfere with comparisons. Trimming a value can make these cases consistent, but it does not standardize spelling, capitalization, punctuation, or word order.
Use TRIM in a helper column
The safest routine is to keep the raw values and clean them in a neighboring column. With the text to clean in A2, enter this in B2:
With the text to clean in A2, the formula for B2 is =TRIM(A2). If A2 contains North Harbor , B2 displays North Harbor.
Press Enter, then fill the formula down alongside the rest of the data. A helper column makes it possible to compare the original and cleaned values before changing anything. If the result is satisfactory and you need permanent text rather than formulas, copy the cleaned cells and use Home > Paste > Values to replace or populate the destination. Keep a copy of the source data when cleanup is difficult to reverse.
For a quick one-off cleanup, select the output cell and enter the formula directly. For a long list, use a helper column and fill down. If the data is in an Excel table, a formula entered in a table column generally fills through that calculated column automatically; check the results at the top, middle, and bottom, especially if the source contains mixed kinds of text.
Clean imported nonbreaking spaces
A frequent reason TRIM appears not to work is that copied text contains a nonbreaking space instead of an ordinary space. Web pages and some exported reports use this character to keep words together on a line. It can look just like a normal space, but TRIM does not treat it as the standard space character.
With the imported text in A2, replace nonbreaking spaces with ordinary spaces before trimming. This formula goes in B2:
With imported text in A2 and the cleaned result in B2, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). If A2 contains North[nonbreaking space]Harbor, where the bracketed description represents one character, B2 returns North Harbor with an ordinary space between the words.
SUBSTITUTE changes each character 160 to a regular space, and TRIM then removes leading and trailing ordinary spaces and compresses repeated ones. This is a practical pattern for many copied web values, but not a guarantee that every unusual character has been removed. Other Unicode whitespace characters may require their own identification and replacement.
When a value looks like it has a gap but neither TRIM nor the replacement above changes it, do not assume the formula is broken. The visible gap may be a different character. Compare the source and output, inspect the origin of the data, or use a targeted replacement once the character is identified.
When CLEAN is useful
CLEAN removes many nonprinting control characters from text; in Excel, its core behavior is aimed at the first 32 ASCII characters. It is often paired with TRIM when text comes from a legacy report or another system that inserts control characters. CLEAN is not a general-purpose remover for every invisible Unicode character, and it does not replace ordinary internal spaces with nothing.
With text in A2 and the cleaned result in B2, use =TRIM(CLEAN(A2)). If the text includes a line-feed control character between two words, CLEAN removes that character; the result may join the words together, so inspect it rather than assuming that removal always creates the intended spacing.
If a line break should become a word separator instead of disappearing, replace it with a regular space before trimming. With text in A2 and the intended result in B2, use =TRIM(SUBSTITUTE(A2,CHAR(10)," ")). CHAR(10) represents a line feed. Some imported text may also contain a carriage return, represented by CHAR(13), or a tab, represented by CHAR(9); whether to replace these with spaces or remove them depends on what the text is meant to say.
For example, converting a line break between a first and last name to a space preserves the separation between the names. Removing the line break with CLEAN could instead join them. The right cleanup is determined by the meaning of the data, not only by whether a character is technically nonprinting.
Check whether cleanup worked
Visual inspection can miss trailing spaces and invisible characters. Compare a few formulas and sample values before deciding the data is ready. Check a short value, a long value, a row that previously failed a match, and examples from each source file or export type. A single clean-looking row does not prove that every row uses the same character encoding.
With the original text in A2 and the proposed cleaned text in B2, use =LEN(A2) in one check cell and =LEN(B2) in another to compare character counts. LEN counts spaces as characters. A shorter cleaned result may indicate that extra spaces or control characters were removed, but a changed count alone does not prove the result is correct: removing a line break or other meaningful character can also shorten text.
With the original value in A2 and the cleaned value in B2, use =A2=B2 to test whether Excel considers them exactly equal. This is useful for finding values changed by cleanup, though a TRUE result does not establish that the text is semantically correct. If you are cleaning data for a lookup, test the cleaned key against the actual lookup list and check for duplicate results created by standardizing different source values.
Common problems and limits
- TRIM leaves a visible gap. The gap may be a nonbreaking space, tab, line break, or another whitespace character rather than an ordinary space. Identify the character and use a specific replacement instead of stacking unverified substitutions.
- Words run together after cleanup. CLEAN can remove a control character that was separating words. If it represents a line break or tab between words, replace it with an ordinary space before applying TRIM.
- Numbers remain text. TRIM cleans text spacing; it does not convert a text value into a numeric value. If a value such as a number imported as text must be used in calculations, handle conversion separately and confirm that the conversion preserves the intended value.
- Only some cells change. A column can mix ordinary spaces, nonbreaking spaces, and values with no extra characters. A formula can therefore change some rows and leave others unchanged. That is normal; assess the results row by row or with targeted checks.
- Cleanup changes distinct labels into duplicates. Two source strings that differ only by extra spaces may become identical after TRIM. Review duplicates before using cleaned text as a unique identifier, because the function cannot decide whether the original difference was accidental.
TRIM also does not remove punctuation, fix misspellings, normalize accented letters, or remove every character that looks blank. It is one step in data preparation, not a complete validation system. For important identifiers, keep the raw value, cleaned value, and a review flag until downstream checks pass.
TRIM formulas versus Find and Replace
A formula is usually the better choice when you need repeatable cleanup, want to preserve the source, or will receive new data that needs the same treatment. It also makes the transformation visible: another person can inspect the formula and see whether it handles ordinary spaces, a specific imported character, or control characters.
Find and Replace is convenient for a known character in a selected range. On Windows, use Ctrl+H to open Replace; on Mac, use Control+H in Excel. Enter the character to find and the replacement, then review the scope and results before choosing Replace All. The exact way to enter an invisible character can be awkward, and replacing every ordinary space with nothing would merge words, so use this method only when the intended change is clear.
For recurring imports or larger repeatable transformations, Power Query offers a data-preparation workflow. In the Power Query Editor, select the text column and choose Transform > Format > Trim. This is useful for trimming values during a refreshable import process. It should not be confused with Excel’s worksheet TRIM formula: check the preview and test internal repeated spaces and unusual imported whitespace to confirm the operation produces the result you need.
Choosing a cleanup method
Choose the smallest method that fits the source and the risk of changing meaningful text. For ordinary accidental spaces, TRIM is simple and clear. For copied web content, test whether CHAR(160) replacement is needed. For legacy control characters, test CLEAN, but decide whether separators should be removed or changed to spaces. For a recurring import, Power Query may be easier to maintain than copying formulas each time.
- Use TRIM for leading, trailing, and repeated ordinary spaces.
- Use SUBSTITUTE with CHAR(160) when a known nonbreaking space is preventing cleanup.
- Use CLEAN for relevant nonprinting control characters, while checking whether removal changes word boundaries.
- Use Find and Replace for a deliberate, one-time change to a known character.
- Use Power Query when the same transformation should be applied during repeated data imports.
Excel for the web supports common worksheet formulas such as TRIM, SUBSTITUTE, CHAR, CLEAN, and LEN, so the formula-based examples also work there. Ribbon placement and available Power Query capabilities can vary by platform and workbook environment. On Mac, formula names and comma-separated arguments are the same in standard Excel settings, but keyboard shortcuts and some data-import features can differ; the Windows instructions above use Microsoft 365 for Windows.
Frequently Asked Questions
Does Excel TRIM remove all spaces?
No. It handles ordinary ASCII spaces: it removes them at the ends and reduces repeated ones inside text to a single space. It does not remove nonbreaking spaces or every other whitespace character.
Why does TRIM not remove spaces copied from a website?
The copied gap may be a nonbreaking space, commonly represented by CHAR(160), rather than an ordinary space. Replacing that character with a regular space before applying TRIM often resolves the issue.
Does TRIM remove spaces between words?
It does not remove all word separators. It reduces multiple ordinary spaces between words to one, so North Harbor becomes North Harbor.
What is the difference between TRIM and CLEAN?
TRIM standardizes ordinary spaces at the start, end, and between words. CLEAN removes certain nonprinting control characters. Neither function removes every possible invisible Unicode character, and CLEAN may remove a separator that you would rather replace with a space.
How do I keep the cleaned text without formulas?
Copy the formula results, then use Home > Paste > Values to place the results as text or values. Check the pasted column before deleting the original data, especially if it contains identifiers or values that must retain their formatting.
Related
- Excel Attendance Format: A Clear Monthly Attendance Tracker
- Excel Attendance File to Print: Set Up and Print It
- Excel Timesheet Format: Build a Reliable Weekly Sheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Round Excel Numbers Without a Formula: Display or ChangeYes. In Excel for Microsoft 365 on Windows, use a number format to display fewer decimal places without…
- Excel Compare Two Sheets: Tools, Formulas, and TipsTo compare two Excel sheets, use Spreadsheet Compare if it is available in your Microsoft 365 or Office…
- Excel IFERROR vs ISERROR: Which Should You Use?Use IFERROR when you want to replace an error with a value such as a message or blank-looking…
- Show Formulas in Excel on iPad: View Cell FormulasIn Excel on iPad, tap a cell to see its formula in the formula bar. To display formulas…
- Excel Right-Click Shortcut: Open the Context MenuIn Excel for Microsoft 365 on Windows, select a cell or range and press Shift+F10 to open its…
- SUMIF in Excel: Practical Examples and FormulasIn Excel for Microsoft 365, SUMIF adds values that meet one condition. Its syntax is =SUMIF(range, criteria, [sum_range]);…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.