To trim a range in Excel, enter =TRIM(A2) beside the first value and fill the formula down; in Microsoft 365, you can also enter =TRIM(A2:A10) in one cell to return a spilled array. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one, but it does not change the source cells unless you replace them with the results.
What TRIM Does to Cell Text
Excel’s TRIM function is for cleaning up ordinary space characters in text. For example, a cell containing several spaces before and after “North Bay,” plus two spaces between the words, returns “North Bay.” This is useful when data copied from a website, report, or another system looks normal but contains spacing that can interfere with sorting, matching, or consistent presentation.
TRIM does not remove every character people informally call whitespace. It is designed to handle the standard space character, ASCII 32. It also does not remove all repeated characters of other kinds, such as nonbreaking spaces, tabs, or line breaks. Those need additional cleanup if they occur in the data.
- Leading spaces at the start of a text value are removed.
- Trailing spaces at the end are removed.
- Multiple ordinary spaces between words become a single space.
- Text that contains no extra ordinary spaces is returned without a visible change.
That third behavior matters: TRIM is not just a command to delete spaces at the edges. If a name contains a deliberate double space, TRIM reduces it to one. Check the data’s intended formatting before applying the results permanently.
Trim a Range with a Formula and Fill Down
The most dependable approach for a list is to use a helper column. It preserves the original values while you review the cleaned results, and it works in Excel for Microsoft 365 on Windows as well as older Excel versions that support TRIM.
- Assume the text to clean is in A2:A10 and column B is available. Select B2.
- Enter
=TRIM(A2)and press Enter. If A2 contains “ West Ridge ” with two spaces between the words, B2 returns “West Ridge.” - Select B2 and fill the formula down through B10. You can drag the fill handle, use Home > Fill > Down after selecting B2:B10, or copy B2 and paste into the remaining cells.
- Review the output for errors or unexpected changes before using it in reports or replacing the original data.
When the source range is part of an Excel table, entering the formula in the first row of a new table column will commonly fill the formula through that calculated column automatically. Confirm that the formula references the right source column and that the results extend through all intended rows.
Trim a Range with a Spilled Formula
Microsoft 365 supports dynamic arrays, so a formula can process a range and return multiple results from one cell. With source text in A2:A10, enter =TRIM(A2:A10) in an empty cell outside the source range. Excel returns the cleaned values in the cells below the formula, one result for each source cell. If A2 contains “ West Ridge ”, its corresponding result is “West Ridge.”
The output cells are a spill range: they are results of the single formula, not independent formulas that can be edited one by one. The cells below the formula must be empty, or Excel may show a #SPILL! error. Clear the obstructing cells or move the formula to an area with enough room. If your Excel version does not support this dynamic-array behavior, use the helper-column method and fill down instead.
A spilled result is convenient for a clean view or a further calculation, but it does not overwrite the source range. It also depends on the source range remaining available. If you need fixed cleaned values, copy the results and paste values into the intended destination.
Replace the Original Values Safely
Formulas are usually the safest first step because you can compare the cleaned text with the original. Once you have confirmed the output, replace the source only if that is what the workbook requires. Make a copy of the workbook first if the source data is important or difficult to recover.
- Copy the cleaned helper-column results or the full spilled output.
- Select the destination cells, usually the original source range.
- Use Home > Paste > Paste Values, or the equivalent Paste Special option in your Excel version.
- Check a few cells, then remove the helper column if it is no longer needed.
Pasting values is important because copying formulas back over their own source cells can create circular references or replace the formula’s inputs. Paste Values transfers the displayed text rather than the formula. If the cleaned range includes blanks, confirm that the paste operation will not unintentionally erase meaningful content elsewhere.
Clean Nonbreaking Spaces and Other Characters
A common reason TRIM seems not to work is that the visible gap is not an ordinary space. Web pages and exported files often contain a nonbreaking space, character code 160, which looks much like a normal space but is not removed by TRIM alone. A formula can first substitute that character with an ordinary space, then clean control characters and trim the result.
Assume the original text is in A2. Enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in B2. If A2 contains “ West Ridge ” with nonbreaking spaces around the words and an ordinary repeated space between them, the formula changes the nonbreaking spaces to ordinary spaces, removes control characters handled by CLEAN, and returns “West Ridge.” Fill the formula down to process the rest of the range.
CLEAN removes many nonprinting ASCII control characters, including common tab and line-break characters, but it is not a universal Unicode text sanitizer. If a troublesome character remains, identify its actual character code or use a suitable import-cleaning step rather than assuming every invisible character is the same. For example, CHAR(160) specifically targets the common nonbreaking space; it does not target every unusual space used in international text.
Using Power Query for Repeatable Cleanup
If you regularly import a large file and need to clean the same column each time, Power Query can make the process repeatable. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range. If Excel asks whether to create a table, confirm the range and headers are correct. In the Power Query Editor, select the relevant text column and use Transform > Format > Trim, then choose Home > Close & Load to return the result to Excel.
Power Query’s Trim operation removes spaces at the beginning and end of text; do not assume it performs the same internal-space collapsing as the Excel worksheet TRIM function. If repeated spaces inside words must also be reduced, add an appropriate transformation or use a worksheet formula for that requirement, then verify the output. Power Query is particularly useful when the source is refreshed periodically because the transformation steps can be applied again on refresh.
Power Query availability and interface details can vary by platform and Excel build. Excel for the web has more limited data-import and query-editing capabilities than desktop Excel, and Mac features can differ from Windows. If the described editor path is not available in your build, use a helper column with TRIM, which is broadly supported.
Why TRIM Might Not Seem to Work
When the output still looks wrong, investigate the character itself and the formula’s destination. Invisible characters can appear identical on screen, so a result that looks unchanged does not necessarily mean the formula failed.
- Nonbreaking spaces: Replace CHAR(160) with a normal space before applying TRIM, as in the formula above.
- Line breaks or tabs: CLEAN can remove common control characters. If you need to retain line breaks for layout, do not use CLEAN without checking the consequences.
- Numbers and dates: Excel stores many numbers and dates as numeric values, not text. TRIM is a text-cleaning tool; it does not correct a number stored as text or change a date’s underlying value.
- Formula result still has a space: Check whether the source contains a different Unicode whitespace character. TRIM only handles ordinary spaces as described, not all Unicode whitespace.
- Spill error: For a range formula in Microsoft 365, check that the output cells are empty and that the formula is not inside an Excel table, where spilled-array formulas are not supported in the usual way.
It can also help to compare results in a lookup or matching task rather than judging by appearance alone. A cell may look identical to another but contain a different invisible character. Clean both sides of a comparison consistently, and keep the raw imported data if you may need to trace where the difference came from.
TRIM, Find and Replace, and Other Options
TRIM is a good choice when the task is specifically to normalize ordinary spaces in text. Find and Replace is better for a known, literal character or phrase that should be changed in a selected range, but it does not apply TRIM’s rule of collapsing any run of ordinary spaces to one. Replacing two spaces with one repeatedly can also leave longer runs partly uncleaned unless the operation is repeated.
Power Query is a useful alternative for a repeatable import workflow, while a formula is often quickest for a one-time cleanup. Text to Columns can help split text using a delimiter, but it is not a general substitute for trimming a range and can change how data is divided. Choose based on whether you need a one-time result, a live formula, or a reusable data-import process.
Frequently Asked Questions
Can I trim an entire column in Excel?
Yes. Enter =TRIM(A2) in a helper column and fill it down as far as needed. In Microsoft 365, a formula such as =TRIM(A2:A1000) can spill results for a defined range when the destination cells are clear.
Does TRIM remove spaces between words?
It reduces multiple ordinary spaces between words to one; it does not remove all spaces between words. It also removes leading and trailing ordinary spaces. Nonbreaking spaces and other whitespace characters may need separate treatment.
Can I use TRIM without a helper column?
Yes, if you enter a range formula in Microsoft 365 and have room for its spilled results. To replace the original values, copy the results and use Paste Values. A helper column is safer when you want to inspect the changes before replacing anything.
Why does TRIM not remove a space copied from a website?
The character may be a nonbreaking space rather than the ordinary space TRIM handles. Try substituting CHAR(160) with a normal space before trimming, then inspect the results for other unusual characters.
Does TRIM remove spaces from numbers?
TRIM works on text. If a value is a true number, there are no text spaces inside its stored numeric value to remove. If a number was imported as text with extra spaces, trim the text first and then convert it only if numeric values are required.
Related
- Excel Data Modeling: How It Works and When to Use It
- Excel Data Modelling: Tables, Relationships and Pivots
- Excel Get Data From a Semantic Model: Connect and Analyze
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- Excel Function to Extract Part of a String: ExamplesExcel has several functions for extracting part of a string: use LEFT for characters at the start, RIGHT…
- Excel Greater Than or Equal To: Formula and ExamplesIn Excel, the greater-than-or-equal-to operator is >=. Put it between two values in a formula, such as =A1>=B1,…
- Excel IF SUM: How to Combine IF, SUMIF, and SUMIFS FormulasTo test a total and return one result or another, combine IF with SUM: for example, with amounts…
- COUNTIFS in Excel: Exact and Partial Text MatchesYes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple")…
- Insert a Row in Excel With a Formula: Real OptionsA formula cannot insert a physical worksheet row or move existing cells. To show a blank row inside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.