Trim Ends in Excel: Remove Extra Spaces

Formulas & functions · Updated November 20, 2026

To trim spaces in Excel, enter =TRIM(A2) in another cell, replacing A2 with the cell containing your text. TRIM removes ordinary spaces from the beginning and end, and also reduces repeated spaces between words to one; if you need to preserve internal spacing or remove unusual web spaces, use a different method described below.

What TRIM Does in Excel

Excel’s TRIM function is designed to tidy text by removing the standard space character (ASCII 32). It clears any run of those spaces before the first visible character or after the last one. It also changes a run of spaces between words into a single space. That last behavior is useful for cleaning imported names and descriptions, but it matters if repeated spaces inside text are intentional.

For example, text that looks like North Ridge becomes North Ridge. Text with no extra ordinary spaces is returned unchanged. TRIM works on text values and on numbers stored as text; it does not alter the original cell when used in a formula. The cleaned result appears in the formula cell.

TRIM does not remove every character that looks like a blank. A nonbreaking space copied from a web page, for example, can look identical to an ordinary space while remaining in the cell. Tabs, line breaks, and other invisible characters can also cause problems. If TRIM seems to have no effect, the issue may be the character type rather than the formula.

Use the TRIM Formula on a Cell

For a one-off cleanup, put the formula beside the source text and fill it down for the rest of the list. With the original text in A2, enter =TRIM(A2) in B2. If A2 contains two leading spaces, two spaces between “North” and “Ridge,” and one trailing space, B2 returns North Ridge. The formula uses a relative reference, so dragging or copying it down changes A2 to A3, A4, and so on.

In Excel for Microsoft 365 on Windows, select the formula cell, then drag its fill handle down the column. If the adjacent data forms a continuous block, double-clicking the fill handle often fills the formula down alongside it. Check the last few rows afterward, especially if the neighboring column has blanks that could stop the fill early. Excel for Mac and Excel for the web use the same TRIM formula; the formula syntax does not change.

To clean a single cell without keeping a formula, you can copy the formula results and replace the original text with values. Select the cleaned cells, copy them, then use Home > Paste > Values. Before replacing the source, verify a few results and consider keeping a backup column. Pasting values removes the formulas, so later changes to the original text will not flow through automatically.

Trimming Only the Ends Versus Cleaning All Spaces

The word “trim” can mean removing spaces at the edges while leaving the text between them exactly as typed. Excel’s TRIM is broader: it also collapses multiple internal ordinary spaces. If you are cleaning a list of names, addresses, or product descriptions, that is usually helpful. If spacing inside the text carries meaning—for example, aligned text imported from a fixed-width report—TRIM may change more than you want.

For an end-only cleanup, Power Query is often a more suitable built-in option because its Trim transformation removes spaces at the edges without reducing repeated spaces inside the text. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range. In the Power Query Editor, select the relevant text column and choose Transform > Format > Trim, then select Home > Close & Load. If Excel asks to create a table, confirm the data range and whether it has headers.

Power Query produces a refreshed output rather than changing the source cells in place. That is useful for recurring imports: when the source data changes, use Refresh to apply the same cleanup again. Menu availability and Power Query support can vary by Excel version and platform, so if those commands are missing, a TRIM formula or a manual cleanup may be more practical. Remember that TRIM’s behavior is different if preserving repeated internal spaces is essential.

Remove Nonbreaking Spaces from Web or Copied Text

Data copied from websites, PDFs, and some systems may contain nonbreaking spaces. These are commonly represented by character code 160 and prevent a line break between neighboring words. They can look like ordinary spaces in a cell, but TRIM alone does not remove them. Replace that character with an ordinary space first, then apply TRIM.

With the suspect text in A2, enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in another cell. If A2 contains a nonbreaking space between “Elm” and “Street,” plus ordinary spaces at either end, the result is Elm Street. SUBSTITUTE changes the character with code 160 into a normal space; TRIM then removes edge spaces and collapses repeated ordinary spaces. Using a normal space as the replacement helps avoid joining words together.

This formula targets the common nonbreaking space, not every possible Unicode spacing character. Some sources use other invisible characters, such as narrow nonbreaking spaces, and these may need their own replacement step. If you suspect a wider range of characters, inspect a sample of the source or clean it in the system that produced it before relying on a single formula across a large dataset.

Handle Tabs, Line Breaks, and Other Hidden Characters

When text comes from a report or a pasted document, the unwanted character may be a tab or a line break rather than a space. Excel’s CLEAN function removes many ASCII control characters, including common tabs and line breaks, but it does not remove every nonprinting Unicode character. CLEAN can also remove a separator between words instead of replacing it with a space, potentially joining the words.

With the text in A2, =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) replaces common nonbreaking spaces, removes supported control characters, and then applies TRIM. For example, if A2 contains North, a line break, and then Ridge, CLEAN may remove the line break and return NorthRidge, not North Ridge. Check the result before using this combination on descriptive text.

If a tab or line break should act as a word separator, replace that character with an ordinary space before cleaning and trimming. For instance, CHAR(9) represents a tab, CHAR(10) a line feed, and CHAR(13) a carriage return. A carefully designed SUBSTITUTE sequence can convert the specific characters in your source to spaces. Test it on a few representative rows first; a formula that is right for one import format may not be right for another.

Clean a Whole Column and Preserve Your Data

For a typical list, use a helper column rather than editing the source immediately. If text is in A2:A500, put the chosen cleanup formula in B2, fill it down, and compare several outputs with their originals. Look for changed internal spacing, lost separators, and values that contain unusual punctuation. Once the output is correct, copy B2:B500 and paste values over the source only if you are sure the cleaned data should replace it.

  • Keep a source copy: duplicate the worksheet or retain the original column until the cleaned results have been checked.
  • Check important edge cases: review blank cells, single-word entries, names with multiple parts, and text copied from websites.
  • Confirm the data type: trimming text does not turn every text-formatted number into a numeric value. If you need numbers for calculations, check the result and convert types separately.
  • Recheck lookups: cleaned keys may now match correctly, but changing text can affect formulas, sorting, and other references that use the original values.

For a list that is imported repeatedly, Power Query is generally easier to maintain than copying formulas each time. For a small, one-time list, a helper column is transparent and easy to audit. Avoid using Find and Replace to remove every space from a whole column: that would also remove spaces inside phrases and could turn New York into NewYork.

Common Problems and How to Diagnose Them

If the result looks unchanged, first check whether the visible gap is an ordinary space or a nonbreaking space. Try the SUBSTITUTE and CHAR(160) formula on a sample. If there is still a problem, the content may contain another Unicode character or formatting that makes the text look different from its actual value. A formula bar inspection can help, but invisible characters are not always obvious there.

If TRIM removes spacing you wanted to keep, that is expected: it reduces repeated ordinary spaces between words. Use an edge-only method, such as Power Query’s Trim transformation, when interior spacing must remain. Conversely, if extra spaces remain after TRIM, check for tabs, line breaks, nonbreaking spaces, or other unusual characters rather than repeatedly applying TRIM.

Also distinguish cell content from visual alignment. A value may appear offset because of cell alignment, indentation, or formatting even when there are no leading spaces in the text. TRIM changes the underlying text value; it does not change alignment settings, column width, or indentation. If the formula result is already clean but the display still looks wrong, inspect the cell’s formatting instead.

Frequently Asked Questions

Does Excel TRIM remove spaces at both ends?
Yes. TRIM removes leading and trailing ordinary spaces. It also reduces repeated ordinary spaces between words to one.

Why does TRIM not remove all spaces copied from a website?
Some copied text contains nonbreaking spaces, often character code 160, rather than ordinary spaces. Replace them with SUBSTITUTE and CHAR(160), then apply TRIM.

Can I trim spaces without changing spaces between words?
TRIM itself cannot do that because it collapses repeated internal spaces. Use Power Query’s Transform > Format > Trim operation for an edge-only cleanup, and verify that it preserves the internal spacing you need.

Can I apply TRIM to a whole column at once?
Enter a formula in a helper column and fill it down, or use Power Query for a repeatable transformation. Check the filled results before replacing the original values.

Does TRIM work in Excel for Mac and Excel for the web?
Yes. The =TRIM(A2) formula works in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon locations and some Power Query options can differ by platform.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.