Open a workbook and remove the spaces at the start and end of text cells, the extra spaces between words, and the non-breaking and zero-width spaces that Excel's TRIM leaves behind. See every change first, then download your own file with only those values changed.
Nothing is uploaded. Your file is edited in your browser and never sent anywhere.
Trim Spaces is a free tool to remove spaces in Excel workbooks without helper columns or formulas, cleaning the text cells of one sheet or all of them in a single pass. The usual file is a supplier price list pasted from a web page, where every VLOOKUP against it returns #N/A because "Blue Widget" ends in a space you can't see.
Only text cells change. Numbers, dates, TRUE/FALSE, error values and every cell with a formula are left exactly as they were.
| In a text cell | This tool (defaults) | Excel's TRIM |
|---|---|---|
| Spaces at the start or end | Removed, always | Removed |
| Two or more spaces between words | Cut to one | Cut to one |
| Non-breaking space (character 160) and other Unicode spaces | Turned into a normal space, then trimmed | Left in place |
| Zero-width space | Deleted | Left in place |
| Zero-width joiner | Kept | Left in place |
| Line break (Alt+Enter) or tab | Kept; turned into a space with the option ticked | Left in place |
| Nothing but spaces | Becomes an empty cell, formatting kept | Returns empty text |
A cell with rich text, say a bold product name followed by a grey note, keeps each part's formatting after its spaces go. With "All sheets" chosen, hidden sheets are cleaned too.
The rest of the workbook keeps its exact bytes: formulas, formatting, charts, pivot tables, names and comments. No document properties are added. Excel recalculates the workbook when you open the cleaned file, so a MATCH that failed on a stray space shows its new result without you pressing anything.
We checked the output in Excel for Mac. On the test workbook, every cleaned cell equalled this formula applied to the original, and the file opened without a repair prompt:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))That formula covers ordinary and non-breaking spaces. Zero-width spaces need a SUBSTITUTE of their own, below.
TRIM removes one character: the ordinary space, code 32. Microsoft's own help page says that by itself TRIM does not remove the non-breaking space, code 160, which web pages use to keep words together. Text copied from a website, a PDF or a chat app can carry either of two lookalikes that TRIM walks past.
The fix most forum answers give is =SUBSTITUTE(A2,CHAR(160),""), which deletes the character. That works for a trailing one. Between words it glues them together: "New York" becomes "NewYork", and the lookup against "New York" still fails. Replace it with an ordinary space and let TRIM tidy up, which is what the formula above does. The tool does the same, and treats the other Unicode space characters the same way.
A zero-width space takes up no room on screen, so the cell looks perfect. LEN counts it, TRIM ignores it, and CLEAN ignores it too, because CLEAN only removes codes 0 to 31. In Excel the fix is =SUBSTITUTE(A2,UNICHAR(8203),""). The tool deletes zero-width spaces (U+200B, U+2060 and the byte-order mark U+FEFF) and keeps zero-width joiners, which Persian and Indic text and emoji need.
=LEN(A2)-LEN(TRIM(A2))
=UNICODE(RIGHT(A2))The first counts the ordinary spaces TRIM would remove. If it returns 0 and two cells still refuse to match, the culprit is something else, and the second formula names the last character: 32 is a space, 160 a non-breaking space, 8203 a zero-width space. Use MID(A2,n,1) in place of RIGHT(A2) to check a character in the middle.
A cell holding only spaces looks blank and isn't. COUNTA counts it, ISBLANK returns FALSE, and Home > Find & Select > Go To Special > Blanks skips it. Pasting TRIM results back as values doesn't cure it: the cell now holds empty text, which COUNTA still counts.
The tool writes a truly empty cell instead, keeping its fill and borders, and the preview and result say how many cells that happened to. Rows that held nothing but spaces then count as blank for Remove Blank Rows, which treats a row as blank only when no cell in it holds a value or a formula.
Line breaks stay unless you tick "Turn line breaks and tabs into spaces". A delivery address typed over two lines with Alt+Enter keeps both lines by default. With the option ticked, "12 High Street" and "Leeds" become "12 High Street Leeds": each break or tab turns into a space, then the text is trimmed. Excel's CLEAN deletes the break instead and gives you "12 High StreetLeeds".
For one column, a helper column is fine. Type =TRIM(A2) next to the data (or the SUBSTITUTE version above for web text), fill it down, copy it, then Home > Paste > Paste Values over the original column and delete the helper. That is one round per column, repeated on every sheet, and Paste Values also drops any in-cell formatting, such as one bold word.
Find & Replace (Ctrl+H) is quicker and blunter. Replacing two spaces with one, then clicking Replace All until Excel finds nothing, collapses the gaps between words but leaves one space at each end. Replacing one space with nothing removes every space, words included.
Power Query's Transform > Format > Trim removes spaces at the start and end only. Unlike the worksheet TRIM, it leaves double spaces between words alone.
Keep the formula route when new rows keep arriving: a TRIM column cleans each one as it lands, where this tool cleans the file once.
=CLEAN(A2) in Excel for those.=VALUE(A2).Once the spaces are gone, "Ann Lee " and "Ann Lee" are the same text, so Remove Duplicates and Compare Two Columns treat them as one value.
Because the spaces left behind are not ordinary spaces. TRIM removes only character 32, so a non-breaking space (character 160, common in text copied from web pages) or a zero-width space passes straight through. Replace the non-breaking space with an ordinary one using SUBSTITUTE before TRIM runs, or clean the file with this tool, which handles both by default.
Put =TRIM(A2) in an empty column, fill it down, then copy it and paste it as values over the original column. TRIM removes spaces at both ends and cuts runs between words to one. If a trailing space survives, it is almost always a non-breaking space, which TRIM ignores.
Load the data into Power Query and use Transform > Format > Trim, which removes spaces at the start and end and nothing else. Find and Replace can't do it cleanly, because replacing a space with nothing also deletes the spaces between words. This tool removes leading spaces from the text cells on the sheets you pick and never removes the space between two words.
Select the cells, press Ctrl+H, type one space in Find what, leave Replace with empty and click Replace All. That deletes every ordinary space, so use it on phone numbers and product codes, not on names. This tool never does that: it never removes the space between two words.
No. TRIM removes spaces only, so a line break typed with Alt+Enter stays in the cell. CLEAN deletes line breaks, but it joins the words on either side into one. This tool's Turn line breaks and tabs into spaces option replaces each one with a space and then trims.
The cell holds one or more spaces. COUNTA counts it, ISBLANK returns FALSE and LEN returns the number of spaces. Pasting TRIM results back as values doesn't fully fix it, because the pasted empty text is still counted by COUNTA; this tool turns a cell of only spaces into a truly empty cell and keeps its formatting.
One side usually has a trailing space or a non-breaking space that the other doesn't. Compare LEN on the two cells: if the numbers differ, the text differs. Clean both the lookup values and the table, and the formulas show new results when Excel recalculates the cleaned file on opening.