Microsoft Excel’s TRIM function removes leading and trailing ordinary spaces from text and reduces repeated spaces between words to a single space. In Microsoft 365 for Windows, enter =TRIM(A2) in another cell to clean the text in A2; TRIM returns the cleaned result without changing the original cell.
What Excel TRIM does
TRIM is a text function for tidying spacing in a cell. It removes spaces at the start and end of a text string, then turns each run of multiple ordinary spaces between words into one space. For example, a name with three spaces between its first and last parts becomes a name with one space between them.
This behavior is useful when text comes from a website, a downloaded report, a customer list, or a file created by another system. Extra spaces can make otherwise identical entries look different to Excel. A name with a trailing space, for instance, may not match the same name without it in an exact-match lookup or comparison. TRIM can make these values more consistent.
TRIM works on text and returns text. It does not remove punctuation, standardize capitalization, or convert a number stored as text into a numeric value. It also does not remove every character that may look like a space; imported data can contain different whitespace characters, discussed below.
Syntax and a basic example
The function has one required argument: the text you want to clean. That argument can be a cell reference, a quoted string, or another expression that returns text.
With the text Maya Chen in A2, enter this formula in B2: =TRIM(A2). The result in B2 is Maya Chen: the spaces at both ends are gone, and the three spaces between the names have become one.
To apply the formula to more rows, put it in the first row of a results column and fill it down. In Microsoft 365 for Windows, you can drag the fill handle at the lower-right corner of the cell, or copy the formula and paste it into the other result cells. Excel adjusts the relative reference, so the next row uses A3, then A4, and so on. On Mac, the function and formula behavior are the same; ribbon labels and some keyboard shortcuts can differ. Excel for the web also supports TRIM.
The original data remains in column A. If you need a cleaned column to replace the original, copy the results and use Paste Special > Values to paste the calculated text over the source, after checking that the results are correct. Keeping the original column until you verify the cleanup makes it easier to recover from an unexpected change.
How TRIM treats different spaces
Excel TRIM is based on the ordinary space character, whose character code is 32. It removes that character at the beginning and end and consolidates repeated instances inside the text. It does not treat every whitespace character as an ordinary space. A tab, a line break, or a nonbreaking space can remain even when it looks like a blank gap on the screen.
Nonbreaking spaces are a frequent source of confusion. They are often present in text copied from webpages or exported from other applications. The common nonbreaking space has character code 160, and a cell containing it may appear to have normal spacing while TRIM leaves it untouched. A useful repair is to replace those characters with ordinary spaces first, then run TRIM.
With the imported text in A2 and nonbreaking spaces represented in that cell by character code 160, enter this formula in B2: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). The formula replaces each character 160 with an ordinary space, then TRIM removes the outer spaces and condenses repeated spaces. If A2 contains Maya[NBSP]Chen, where [NBSP] stands for one nonbreaking space, the result is Maya Chen.
Tabs and line breaks are different again. Excel’s CLEAN function removes many nonprinting ASCII control characters, including common line breaks and tabs, but it does not remove every Unicode character that may be invisible. Combining CLEAN with TRIM can help with pasted or exported text that contains both control characters and extra ordinary spaces.
With text in A2 that may include tabs or line breaks as well as extra ordinary spaces, enter this formula in B2: =TRIM(CLEAN(A2)). For a cell containing Maya, a line break, and then Chen, the line break is removed by CLEAN and TRIM returns MayaChen. If you want a visible separator between the parts, replace the line break with an ordinary space before applying TRIM; CLEAN alone removes it rather than substituting a space.
Cleaning imported text more thoroughly
When a cleanup formula needs to handle both nonbreaking spaces and common control characters, combine SUBSTITUTE, CHAR, CLEAN, and TRIM. The order matters: replace the nonbreaking space with an ordinary space, remove supported control characters, and finally normalize ordinary spaces.
With imported text in A2 that may contain character 160, tabs, line breaks, or repeated ordinary spaces, enter this formula in B2: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). If A2 contains the words Maya and Chen separated by a nonbreaking space and surrounded by ordinary spaces, the result is Maya Chen.
This is a practical cleanup, not a universal solution for every invisible character. Unicode includes other spacing characters that CHAR(160) and CLEAN may not address. If a value still refuses to match after this formula, inspect a sample carefully and identify the character causing the problem rather than assuming that all blank-looking marks are identical. A formula can only replace the characters it is designed to recognize.
Using TRIM in a data-cleaning workflow
For a list of names, addresses, product labels, or categories, a separate results column is a straightforward way to clean data safely. A typical workflow is:
- Keep a copy of the original column, especially if the data came from an external source.
- Enter a TRIM formula in the first row of a new column, referring to the corresponding source cell.
- Fill the formula down through the records you want to clean.
- Review a few results, including rows that previously had inconsistent spacing or failed to match.
- If needed, copy the cleaned results and paste them as values into a destination column.
For a quick, one-off repair, Find and Replace can change a known pattern, but it is less dependable when the number of spaces varies from row to row. TRIM handles different runs of ordinary spaces consistently and leaves the source untouched when used in a separate column. For recurring imports, a repeatable formula or a data transformation process is usually easier to audit than manual editing.
TRIM can also be used inside another formula when a cleaned value is needed as part of a calculation or comparison. For clarity and easier troubleshooting, however, a dedicated helper column is often preferable when cleaning a large list. It lets you compare the original and cleaned text side by side and find unusual cases before replacing anything.
TRIM compared with CLEAN and SUBSTITUTE
These functions address different problems, so they are not interchangeable. TRIM handles ordinary spaces. CLEAN removes certain nonprinting control characters. SUBSTITUTE replaces a specified piece of text or character with another one. Combining them is often more effective than expecting any one function to repair every type of imported whitespace.
- TRIM: removes outer ordinary spaces and reduces repeated ordinary spaces between words to one.
- CLEAN: removes many nonprinting ASCII control characters, such as common tabs and line breaks; it does not normalize ordinary spaces or remove every Unicode whitespace character.
- SUBSTITUTE: replaces a chosen character or text string. For example, it can replace character 160 with an ordinary space before TRIM runs.
These functions do not make data equivalent in every other respect. TRIM does not change uppercase letters to lowercase, remove accents, standardize hyphens, or strip punctuation. If two entries differ in those ways, they need a different, deliberate normalization step. Cleaning only the spacing is safer than making broader changes that could alter meaningful text.
Common problems and what to check
If TRIM appears to do nothing, first check whether the blank-looking characters are ordinary spaces. A stubborn gap may be a nonbreaking space, a tab, or another character that the function does not handle. Try the SUBSTITUTE-and-TRIM approach for character 160, or CLEAN for common control characters. If neither works, compare the underlying characters rather than relying on how the cell looks.
If a formula displays instead of calculating, check that the cell is not formatted as Text and that the entry begins with an equals sign. You can change the cell format to General and re-enter the formula. Also confirm that Show Formulas has not been turned on, since that setting displays formulas in worksheet cells rather than their results.
If the cleaned result looks right but a lookup still fails, spaces may not be the only difference. The source and lookup value could differ in punctuation, spelling, hidden Unicode marks, or data type. TRIM does not convert a text number such as 00125 into a numeric value, and converting it may remove meaningful leading zeros. Check the actual requirements of the data before changing types or removing characters.
Finally, remember that entering TRIM in a new column does not alter the source. This is usually helpful, but it can surprise someone expecting the original cells to change immediately. Review the cleaned results, then copy and paste values only if replacing the source data is truly required.
Frequently Asked Questions
Does Excel TRIM remove spaces between words?
Yes. It reduces each run of repeated ordinary spaces between words to one space. It also removes ordinary spaces at the start and end of the text.
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 an ordinary space using SUBSTITUTE and CHAR, then apply TRIM.
Does TRIM change the original cell?
No. A formula such as =TRIM(A2) returns a cleaned result in the formula cell and leaves A2 unchanged. To replace the source, copy the results and paste values after checking them.
Can TRIM remove line breaks or tabs?
TRIM is not designed to remove them. CLEAN removes many common nonprinting control characters, including common tabs and line breaks. If removing a line break joins words that should remain separate, replace it with an ordinary space before trimming.
Does TRIM remove all extra spaces in Excel?
No. It handles ordinary spaces, not every character that looks like whitespace. Nonbreaking spaces and other Unicode characters may need a targeted replacement or a different cleanup method.
Is Excel TRIM available on Mac and the web?
Yes. TRIM is a standard Excel function and can be used in Excel for Microsoft 365 on Windows, Mac, and the web. The formula syntax is the same, though some interface details vary by platform.
Related
- Keyboard Shortcut to Jump Tabs in Excel
- Excel Tab Rename Shortcut: Windows and Mac Steps
- Excel Keyboard Shortcuts to Resize Rows and Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Max Index: Row, Column, and INDEX Function LimitsIn current Microsoft Excel for Microsoft 365, the largest worksheet row index is 1,048,576, and the largest column…
- SharePoint Excel Formula Not Calculating? Fixes That WorkIf an Excel formula in a SharePoint workbook is not calculating, first confirm that the cell contains a…
- EDATE in Excel: Add Months to a Date, With ExamplesTo add calendar months to a date in Excel, use =EDATE(start_date, months). For example, with a date in…
- Extract Day from Date in Excel: Formula and StepsTo extract the day of the month from an Excel date, put the date in A2 and enter…
- COUNTIF Excel Formula for Dates: Examples and FixesTo count cells that contain a particular date, use Excel’s COUNTIF function with a real date value as…
- Excel Formula to Calculate Hours WorkedFor a shift that starts in B2, ends in C2, and has an unpaid break entered as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.