Docs · guide

Remove spaces in Excel, and the one TRIM does not touch

By Alberto Gulotta · Updated 29 September 2026 · 20 min read

To remove spaces in Excel, put =TRIM(A2) in a new column B beside the data, fill it down, paste that column over itself as values, and only then delete the original column A. TRIM was designed for the ASCII space, value 32. Text copied from a web page can carry the nonbreaking space at 160, and Microsoft says TRIM alone leaves it.

1. Back up the original data. On Top ten ways to clean your data the backup is not the opening move but the second of the basic steps, straight after importing the data from an external source: copy the original into a separate workbook. Step 5 below deletes the column you started from.
2. Put the formula in a new column beside the data. Insert a new column next to column A, type =TRIM(A2) in cell B2, press Enter, and fill the column down. Microsoft writes those moves more than once, and never with the same detail. Top ten puts them in prose first, then numbers them, and prints no formula either time: insert a new column B beside the original column A, add a formula at the top of B that transforms the data, fill it down. The 2010 VLOOKUP card writes the same moves out in prose as well, and it is the card — not Top ten — that names the formula, the cell B2 and the ENTER key.
3. Read the new column before anything is pasted over it. This is the last point at which the formula exists, and so the last at which it can be changed. If the spaces are still there, one documented reason is that the character is not the one TRIM was built for, and the formula that takes that one too is =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))): SUBSTITUTE turns CHAR(160) into an ordinary space, CLEAN removes nonprinting values 0 to 31, and TRIM the extra spaces. Put it in B2 instead and fill down again. The formula is the 2010 card’s; looking before pasting is this guide’s order, and no page read for it tells you to look: the 2010 card only pastes “After the spaces are gone”. On a Mac, note the gap: the Excel for Mac errors page sends readers to that same card, while the CODE page says its codes come from the Macintosh character set on a Mac and ANSI on Windows — and nothing read here says what CHAR(160) returns on a Mac.
4. Paste the new column over itself as values, not formulas. A formula column still points at the untidy one, so the formulas have to go and the results have to stay. Top ten’s step keeps everything inside column B: select the new column, copy it, and paste as values into that same new column, and the original is still there, untouched, until the next step. The 2010 card goes the other way: paste the clean data from column B over the data in column A, values and not the formula, and then delete column B. Both end with the cleaned data under the letter A; the route here is Top ten’s, and step five is the one that deletes. The Paste Special path is the Home tab, the arrow below Paste, then Paste Special and Values, or Ctrl + Shift + V — printed on the Windows tab of Convert numbers stored as text and again on its Web tab, a page that has no Mac tab at all.
5. Remove the original column A. Now, and not before, the untidy column goes. Microsoft says what that does to the letters and stops there: with column A removed, the new column converts from B to A, so the cleaned data ends up under the letter the untidy data had. No page read for this guide says what the deletion does to formulas that pointed at either column.

Find and Replace can close the gaps between words as well. Microsoft’s page opens Replace with Ctrl+H on its Windows and Web tabs and with Control + H on its macOS tab, each through its own Home path — and, on the copy read on 26 September 2026, it never names a space. Nothing on it tells you to type one into Find what, or how to type a nonbreaking one; removing text by “finding instances of that text and then replacing it with no text” is on Top ten ways to clean your data. The pairing is named once more, on the 2010 VLOOKUP card, for unnecessary leading or trailing spaces, or extra spaces between words, in the first column or the lookup value: they can be removed by hand or with the find and replace feature — and the card prints no keystroke for that either. The steps the card leaves out are on datacamp.com: type one or two spaces into Find what, leave Replace with empty and select Replace All, which “removes spaces directly in the selected cells”. With a single space typed, the gaps between words go too — our reading, which datacamp does not state; the option that sounds closest to sparing them is Match entire cell contents, and Find or replace text and numbers publishes it as a way to “search for cells that contain only the characters that you typed in the Find what box” — so it skips every cell holding anything else, which is not the same job as sparing a space inside a cell. It is not the page’s only narrowing option — Match case, the Within box and Format sit beside it — and none of the four names a space, on the copy read on 26 September 2026. The formulas separate the two: SUBSTITUTE with an empty replacement removes every ordinary space, value 32, because “Otherwise, every occurrence of old_text in text is changed to new_text”, while TRIM keeps one by definition.

A space that survives is not cosmetic. Top ten ways to clean your data gives the consequence in one line: “These characters can sometimes cause unexpected results when you sort, filter, or search.” The #N/A page lists “There is extra spacing in the cells” as a named cause and answers it by nesting the cleaning inside the lookup, =VLOOKUP(D2,TRIM(A2:B7),2,FALSE). The condition the page prints for pressing Enter instead is narrow: a current version of Microsoft 365 that is also on the Insiders Fast release channel. Otherwise you select the output range, put the formula in its top-left cell and confirm with Ctrl+Shift+Enter — printed with no platform named, on a page whose Applies To includes Mac, iPad and Excel Web App. The INDEX/MATCH page splits the job correctly, “To remove unexpected characters or hidden spaces, use the CLEAN or TRIM function, respectively”. The VLOOKUP and COUNTIF pages offer the pair without splitting it. VLOOKUP sends readers to CLEAN or TRIM for trailing spaces, and COUNTIF offers the same two for counting text; CLEAN’s own page covers values 0 to 31 and TRIM’s puts the space at 32, so of that pair only TRIM reaches a space. A lookup returning #N/A is one way the leftover space announces itself.

Four names look like the answer, and none of them is documented for spaces in Excel on any page read for this guide. TRIMRANGE “excludes all empty rows and/or columns from the outer edges of a range or array” — rows, not characters — on Excel for Microsoft 365 alone. TRIMENDS removes “leading and trailing spaces, such as whitespace and non-breaking space before and after the text” — the only one of the four to name it — but it is a SharePoint function, listed from SharePoint Server 2019 down to SharePoint Foundation 2010, with no Excel. Text.Trim is Power Query M on Microsoft Learn, where “By default, all the leading and trailing whitespace characters are removed” without listing which characters count; that page has no Applies To line. REGEXREPLACE is real and is in Excel — “By default, occurrence is 0, which replaces all instances” — but it stops at Excel for Microsoft 365 and its Mac version, and the page read for this guide publishes no pattern for a space.

Where this answer stops. Every code, version list and quoted sentence above is read from the page named beside it, on 26 September 2026 — a Microsoft page in every case but two, the wrapper around A1 and the Find and Replace steps, both credited to datacamp.com where they appear. Where a page says nothing, the list above names the gap instead of filling it.

Not covered here. It does not teach the Power Query ribbon: the only Power Query page read here is the Microsoft Learn reference for Text.Trim, which does not list what counts as whitespace.

It does not cover splitting a column into several, which is a different job; the opposite direction is combining text in Excel, and the case rules are in changing case in Excel.

And it does not cover Google Sheets. What holds instead is simple: the codes and version lists here are read from the page named beside each one, Microsoft except the A1 wrapper and the Find and Replace steps credited to datacamp.com, and where a page says nothing this one says so.