Trim Excel Data: Remove Extra Spaces the Right Way

Formulas & functions · Updated November 18, 2026

To remove extra spaces in Excel, enter =TRIM(A2) in a helper column, replacing A2 with the cell you want to clean, then fill the formula down. TRIM removes spaces at the start and end of text and reduces repeated spaces between words to one; for nonbreaking spaces copied from websites, use the more complete formula described below.

What trimming Excel data means

In spreadsheets, “trimming” usually means cleaning unwanted spaces from text so entries match consistently. A cell can look correct while containing a leading or trailing space, such as “North ” instead of “North”. That extra character can prevent an exact lookup from matching, make two apparently identical category names count separately in a pivot table, or cause duplicate checks to miss a repeated value.

Trimming is different from deleting part of a value. It does not remove a word, shorten a number to a chosen length, or delete blank rows. Excel’s TRIM function is specifically about ordinary space characters in text. If you mean removing rows, columns, or characters from a string, those are different cleanup tasks.

Before cleaning a large list, consider making a copy of the source column or workbook. A helper column lets you check the results before replacing the original data, which is useful when cells contain names, codes, or text with intentional spacing.

How Excel’s TRIM function works

With the text to clean in A2, enter =TRIM(A2) in B2. For example, if A2 contains three spaces before “Blue”, two spaces between “Blue” and “Lake”, and one space after “Lake”, the formula returns “Blue Lake”: no leading or trailing spaces and one space between the words.

TRIM changes repeated ordinary spaces inside text as well as spaces at the edges. That behavior is often helpful for names and descriptions, but it matters if repeated spaces have a deliberate purpose, such as aligning text in a plain-text code or preserving a particular layout. In that case, do not apply TRIM without checking the intended format.

The function does not remove every character that looks like a space. In particular, web pages and other applications may use a nonbreaking space, which Excel treats differently from an ordinary space. TRIM also does not remove every kind of invisible character, such as all Unicode formatting marks. If the basic formula leaves a value apparently unchanged, the source may contain one of these other characters.

Clean a column without overwriting the source

For a list in column A, use a new column for the cleaned results. In Microsoft Excel for Microsoft 365 on Windows, these steps work for a typical range:

  1. Insert a blank column next to the data, or choose another empty column for the results.
  2. In the first data row, enter =TRIM(A2), adjusting A2 to the correct source cell.
  3. Press Enter. Select the formula cell and use the fill handle at its lower-right corner to copy the formula down the list. You can also copy the formula and paste it into the remaining rows.
  4. Review a sample of the cleaned values, including entries that previously caused lookup or duplicate problems.
  5. If you want to replace the original text, copy the results and use Home > Paste > Paste Values to paste them over the source column.

On Excel for Mac, the same formula and ribbon commands are available. Keyboard shortcuts differ: Windows uses Ctrl+C to copy, while Mac uses Command+C. The menu command Home > Paste > Paste Values is a clear option on either platform. Pasting values matters because otherwise the copied cells may retain formulas that refer to the original column.

If the data is an Excel table, entering the formula in the first row of a new table column will generally fill the formula through that column automatically. Check the last few rows to confirm the results cover the full dataset.

Handle nonbreaking spaces and hidden characters

When ordinary TRIM does not fix copied text, a common cause is the nonbreaking space, character 160. It often appears in text copied from websites or exported systems. With the source value in A2, this formula replaces those characters with ordinary spaces, removes certain nonprinting control characters, and then trims the result:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

For example, if A2 contains “North”, a nonbreaking space, and “Branch”, followed by extra ordinary spaces, the formula returns “North Branch”. SUBSTITUTE changes character 160 to a normal space, CLEAN removes certain control characters, and TRIM tidies the spaces that remain. CLEAN is not a universal remover of every invisible Unicode character, so unusually sourced data may still need investigation.

Be cautious with CLEAN when line breaks are meaningful. CLEAN removes certain control characters, including line breaks, rather than automatically turning every line break into a separating space. If a line break divides two words, simply removing it can join the words. Inspect the source first, and replace a line break with an ordinary space when that is the intended result.

One way to diagnose a stubborn value is to compare its length with a version after cleaning, or inspect individual characters using Excel’s character functions. If the text comes from an external system, checking the export or import settings may prevent the unwanted character from recurring.

Keep numbers, dates, and identifiers in mind

TRIM is primarily useful for text. If a cell contains a numeric value, it may not have visible spaces to remove, and applying text-cleaning formulas can affect how a value is represented. In particular, an identifier such as 00127 should usually be stored as text if the leading zeros are meaningful. If Excel has already interpreted it as the number 127, TRIM cannot restore the missing zeros.

Dates and numbers can also have different underlying values from their displayed formats. A date may display as “2 Nov 2026” while Excel stores a serial number; changing the display format is not the same as trimming text. If a numeric-looking value was imported as text with spaces, clean it deliberately and verify that any later conversion to a number or date produces the intended result.

For a column that mixes text, numbers, and codes, test the formula on representative rows before applying it throughout. Preserve the original column until you have checked leading zeros, date interpretation, and any formulas that depend on the original values.

TRIM, Find and Replace, and Power Query

The best method depends on the source and scale of the data. A formula is easy to review and repeat, while a data-import workflow can be more suitable for recurring cleanup.

  • TRIM formula: A good choice for a worksheet column when you want to see cleaned results beside the source. It removes leading and trailing ordinary spaces and collapses repeated ordinary spaces between words.
  • Find and Replace: Useful for a known, specific character or text fragment, but replacing double spaces globally can require repeated passes and may change spacing you meant to preserve. It is less precise than a formula when each row needs a controlled transformation.
  • Power Query: Useful for repeatable imports and larger data-cleaning workflows. In the Power Query Editor, select a text column and choose Transform > Format > Trim. Power Query’s trim operation removes whitespace at the beginning and end; unlike Excel’s worksheet TRIM function, it does not generally collapse repeated spaces inside the text.
  • Flash Fill: Useful when the desired output follows a recognizable pattern that you demonstrate in example cells. It is not a dedicated whitespace-cleaning function, so inspect the generated values rather than assuming it has applied the same rule to every entry.

Power Query is particularly practical when the same file is imported and cleaned repeatedly: after setting up the transformation, refresh the query when the source changes. For a one-time list with a few thousand entries, a helper formula may be simpler. Choose based on whether you need repeatability, transparent row-by-row results, or a broader import process.

Common problems and checks

If cleaned values still fail to match, first check whether the formula addressed the correct cells and filled down to the last row. Then look for characters beyond ordinary spaces: nonbreaking spaces, line breaks, tabs, punctuation differences, or inconsistent spelling can all make two strings unequal. TRIM alone is not a general-purpose data standardization tool.

Also distinguish a cleaned formula result from a permanently cleaned value. If you delete the source column while formulas still refer to it, the results may break. Use Paste Values before removing or replacing the source, and keep a backup if the original data may be needed later.

When comparing two values, test them after cleaning both sides rather than cleaning only one. A lookup can still fail if the lookup value contains a hidden character while the list does not. For reliable matching, apply a consistent cleanup approach to both the data being searched and the value used for the search.

Frequently Asked Questions

Does Excel TRIM remove spaces between words?
Yes. It changes repeated ordinary spaces between words to one space. It also removes ordinary spaces at the beginning and end of text.

Why does TRIM not remove some spaces copied from a website?
The text may contain nonbreaking spaces rather than ordinary spaces. Replace character 160 with an ordinary space using SUBSTITUTE, then apply TRIM. The combined formula above handles this common case.

Can I trim a whole column at once?
Enter the formula in the first row of a helper column and fill it down, or enter it in a new column of an Excel table. Review the output, then use Paste Values if you need fixed results instead of formulas.

Does TRIM remove extra spaces inside a sentence?
Yes. For example, “Ready to go” becomes “Ready to go”. If repeated internal spaces are intentional, use another approach or retain the original values.

Is TRIM the same in Excel and Power Query?
No. The worksheet TRIM function also reduces repeated ordinary spaces inside text to one. Power Query’s Transform > Format > Trim removes whitespace at the text’s edges but does not perform that same internal-space reduction.

Can TRIM clean every hidden character?
No. It handles ordinary spaces, and the SUBSTITUTE formula addresses the common nonbreaking-space issue. Other invisible characters may require a targeted replacement or a closer look at how the data was exported.

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.