Last updated July 2026. To remove extra spaces in Excel, use =TRIM(A2). TRIM strips leading and trailing spaces and collapses runs of internal spaces down to one. It does not remove the non-breaking space (character 160) that PDF and web sources leave behind, so when TRIM looks like it did nothing, wrap it: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). That one formula fixes the majority of stubborn space problems on converted data.
Stray spaces are the quiet reason a converted file misbehaves. A lookup returns #N/A even though the value is right there. Two columns that should match do not. A sum reads low because some numbers are really text with a trailing space. None of it is visible, which is why spaces cause more wasted time than almost any other cleanup issue.
How do I remove spaces in Excel?
Put =TRIM(A2) in an empty helper column and fill it down the length of your data. TRIM removes every space before the first word, every space after the last word, and any double or triple spaces between words, leaving single spaces intact. Then copy the helper column, paste it back over the original as Values (right-click, Paste Special, Values), and delete the helper. The cleaned text stays; the formula is gone.
TRIM is deliberately gentle: it keeps the single spaces between words, so "Acme Supply Co" becomes "Acme Supply Co" rather than "AcmeSupplyCo". That is what you want for names and descriptions. It is only when you need to delete every last space that you reach for a different tool, covered below.
Why won't TRIM remove all the spaces?
If TRIM leaves a space that clearly should be gone, the character is not an ordinary space. The usual offender is the non-breaking space, character code 160, which comes from HTML, PDFs, and copied web tables. TRIM only handles the standard space, character 32, so it walks right past 160 and leaves it in place.
The fix is to swap the non-breaking space for a normal one first, then let TRIM do its job: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). SUBSTITUTE finds every character 160 and replaces it with a real space, and TRIM then tidies the result. This is the single most useful space formula for anyone working with converted documents, because CHAR(160) is so common in that data and so invisible on screen.
| Problem | Formula |
|---|---|
| Extra leading, trailing, or double spaces | =TRIM(A2) |
| A space TRIM won't remove (non-breaking space) | =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) |
| Line breaks and other non-printing characters | =TRIM(CLEAN(A2)) |
| Everything at once (the all-in-one) | =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) |
| Remove every space, even between words | =SUBSTITUTE(A2, " ", "") |
How do I remove all spaces, not just the extra ones?
Use =SUBSTITUTE(A2, " ", "") to delete every space in the cell, including the ones between words. This is right for values that should have no spaces at all, like account numbers, SKUs, phone strings, or ID codes that a conversion split with stray spaces. TRIM would keep the single spaces; SUBSTITUTE with an empty replacement removes all of them.
To wipe out non-breaking spaces too, nest the substitutions: =SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), ""), " ", ""). The inner SUBSTITUTE clears character 160, the outer one clears the ordinary spaces, and you are left with a value that has nothing but its real characters.
What is the difference between TRIM and CLEAN?
TRIM removes unwanted spaces; CLEAN removes non-printing characters. CLEAN strips the first 32 control characters in the ASCII set, which includes the line breaks (character 10) that show up when a PDF cell held text on two lines and the conversion crammed it into one. If your cells have invisible breaks that push text onto a second line inside the cell, =CLEAN(A2) removes them. Pair it with TRIM, =TRIM(CLEAN(A2)), to clear both breaks and spaces in one go.
The catch worth remembering: neither TRIM nor CLEAN touches the non-breaking space at 160, because 160 sits outside CLEAN's range and is not a plain space for TRIM. That is why the complete formula is =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))). It handles line breaks, non-printing junk, non-breaking spaces, and extra ordinary spaces all at once, which is the belt-and-braces choice for messy converted text.
How do I remove spaces without a formula?
Find and Replace clears ordinary spaces fast. Select the range, press Ctrl plus H, type a single space in the Find what box, leave Replace with empty, and click Replace All. That deletes every ordinary space in the selection with no helper column. The limitation is the same non-breaking space: Find and Replace will not match it if you just type a space. To catch it, click into the Find what box, hold Alt, and type 0160 on the numeric keypad, then Replace All with the box empty. On a laptop without a numeric keypad, the formula route is easier.
Why do hidden spaces break my lookups?
Excel treats "10045" and "10045 " as two different values, so a trailing space on one side of a match is enough to make it fail. This is the number one reason a VLOOKUP returns #N/A when the value is plainly sitting in the list, and the reason two columns that look identical do not match. The cells look the same to you and different to Excel.
Clean both sides before you match. Run TRIM with the non-breaking-space substitute over your lookup column and your reference column, paste the results back as values, and the matches that failed will start working. When a conversion has left numbers as text with spaces attached, clearing the spaces is often step one and converting text to numbers is step two. Figures pulled cleanly off a source, the way a tool that turns receipts into expense totals aims to, save you this cleanup entirely, but when you inherit a messy file, TRIM and SUBSTITUTE are the reliable rescue.
Spaces are one item on a longer tidy-up list. For the full sequence of fixing a converted file, from spaces to duplicates to text numbers, follow the guide to cleaning up data after a PDF conversion, and start every clean sheet with a good PDF to Excel conversion so there is less to strip out in the first place.