Excel's TRIM function looks broken when the cell contains more than ordinary keyboard spaces. The fastest fix is to clean normal spaces first, then target nonbreaking spaces, hidden imported characters, formula settings, and number text only where they apply.
Use the fixes below in order so you do not rebuild a worksheet around one bad character.
Start with the standard TRIM formula
This method applies when the cells contain regular leading spaces, trailing spaces, or repeated spaces between words. TRIM removes extra ASCII spaces and leaves single spaces between words.
Insert a helper column next to the messy text.
In the first helper cell, type =TRIM(A2), replacing A2 with the first cell you want to clean.
Fill the formula down the column.
Select the helper results and press Ctrl+C.
Select the first original cell, then use Home > Paste > Paste Special > Values.
Remove nonbreaking spaces
This fixes pasted web text or imported text where TRIM returns the same messy-looking value.
Insert a helper column beside the original data.
In the first helper cell, enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Fill the formula down to clean the rest of the column.
Select the helper results and press Ctrl+C.
Select the original destination cells, then use Home > Paste > Paste Special > Values to replace the old text.
Clean hidden imported characters
Insert a new helper column next to the imported text.
Type =TRIM(CLEAN(A2)) in the first helper cell to remove extra spaces and ASCII nonprinting characters from the source cell.
Fill the formula down for the full set of rows.
Copy the helper results.
Use Home > Paste > Paste Special > Values when you want the cleaned cells to stop depending on formulas.
If the data contains both nonbreaking spaces and nonprinting characters, use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) instead.
Switch formulas back to results
Use this when the cell shows =TRIM(A2) instead of the cleaned result. Excel is showing formulas or treating the entry as text.
Open the Formulas tab and select Show Formulas to switch back to formula results.
You can also press Ctrl+`.
If one formula is stored as text, select the cell, press F2, delete the apostrophe before the formula, and press Enter.
When the cell format is the problem, open Format Cells with Ctrl+1, change the number format, then re-enter the formula.
Turn calculation back on
Use this when TRIM worked once but no longer updates after you edit the source cell.
- 1.On Windows desktop, go to File > Options > Formulas.
- 2.Under Calculation options, choose Automatic for Workbook Calculation.
- 3.From the ribbon, use Formulas > Calculation > Calculation Options > Automatic.
- 4.In Excel for the web, open Formulas > Calculation Options > Automatic.
- 5.For a workbook that stays in manual mode, press F9 to recalculate all open workbooks or Shift+F9 to recalculate the active worksheet.
Convert cleaned text to numbers
This fixes values that look clean after TRIM but still act like text in calculations.
Insert a new column beside the values.
Enter =VALUE(TRIM(A2)) in the first cell of the new column.
Fill the formula down with the fill handle.
To replace the originals, select the formula results and press Ctrl+C.
Select the first original cell, then use Home > Paste > Paste Special > Values.
When Excel shows the small error indicator on text-formatted numbers, select the indicator and choose Convert to Number.
Use cleanup tools for larger ranges
For one known bad character, use Ctrl+H on Windows or Control+H on Mac. You can also open Home > Find & Select > Replace, paste the unwanted character in Find what, enter a normal space or leave Replace with blank, then choose Replace or Replace All.
For imported data in Excel for Microsoft 365, load the data into Power Query. Select the text column, then use Add Column > Format > Trim or Add Column > Format > Clean. Use the same commands under Transform when you want to change the original query column.
Flash Fill works when the cleaned result follows a visible pattern. Type the cleaned result in the adjacent column, start the next cleaned value until Excel previews the pattern, then press Enter. You can also run it from Data > Flash Fill or press Ctrl+E.
Update or repair Excel
The most common cause is bad text data, not the TRIM function itself.
On Windows, open Excel, create a new document, then go to File > Account > Product Information > Update Options > Update Now.
If Update Now is missing but Enable Updates appears, select Enable Updates first.
On Mac, open Excel and select Help > Check for Updates.
In Microsoft AutoUpdate, choose Update or Update All, then restart Excel.
If Excel is malfunctioning on Windows, repair Microsoft 365 or Office from Windows settings.
On Windows 11, right-click Start, open Installed apps, select the Microsoft 365 or Office product, choose the ellipsis > Modify, then run Online Repair > Repair.
On a work or school device, missing update controls usually mean Office is volume licensed or managed. Use Microsoft Update when your device allows it, or contact your help desk.
Repair the problem workbook
When TRIM fails only in one file, the workbook is the suspect. Open Excel and go to File > Open.
Choose the location and folder, then select the problem workbook in the Open dialog box.
Select the arrow next to Open, choose Open and Repair, then select Repair.
If that fails, choose Extract Data to recover values and formulas.
If workbook repair does not fix it, return to the source cells and use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")), because nonbreaking spaces are the standard reason TRIM appears to fail after normal spaces have been ruled out.
Frequently Asked Questions
Does TRIM remove every kind of space in Excel?
No. TRIM removes ordinary ASCII space 32. For nonbreaking space 160, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
What does CLEAN remove in Excel?
CLEAN removes ASCII nonprinting characters 0 through 31. It does not remove every Unicode nonprinting character by itself.
How do I keep TRIM results after deleting the helper column?
Copy the TRIM results, then paste them back with Home > Paste > Paste Special > Values so the cells keep the cleaned text instead of formulas.
Can Copilot help write an Excel cleanup formula?
Yes, with an eligible Copilot license. Ask Copilot for a formula to remove extra spaces and nonbreaking spaces, then review and verify the generated result before using it.