How to Trim Whitespace from Cells in Excel (Three Methods)

The whitespace problem nobody warned you about

You ran a VLOOKUP. It returned #N/A even though you can clearly see the value in both columns. You stared at the screen for ten minutes. The fix turned out to be a single trailing space in one of the cells. Welcome to the most annoying class of Excel data bug: invisible whitespace.

This is the most common reason "it should match but doesn't" happens in Excel. The good news: there are three solid ways to fix it. The right one depends on whether you're cleaning data once or every week, whether you can use formulas, and how your data was contaminated in the first place.

TL;DR — Key takeaways

• The most common Excel whitespace problems are leading spaces, trailing spaces, double spaces between words, and non-breaking spaces (Chr(160)) from web copy-paste.
• =TRIM() handles leading, trailing, and double spaces but does NOT remove non-breaking spaces.
• Find/Replace can handle non-breaking spaces if you know the trick (Alt+0160 on Windows).
• A VBA macro is the only one-click fix that handles all four whitespace categories at once.
• Whitespace bugs cause silent VLOOKUP/MATCH/COUNTIF mismatches, not error messages, which is why they're so hard to spot.

Where invisible whitespace comes from

A column that looks like Acme Co and a column that looks like Acme Co can be different strings. Common reasons:

• Trailing space. A user typed Acme Co (with a trailing space) and Excel happily accepted it.
• Leading space. Someone copy-pasted from a web page where the source had a leading space inside a <td>.
• Double internal spaces. Acme Co (with two spaces between words) is different from Acme Co.
• Non-breaking space (U+00A0, ASCII 160). Web pages and Word documents use these. They look exactly like regular spaces but Excel treats them as a different character. =A2=" Acme Co" returns FALSE because the leading "space" is actually a non-breaking space.

Each of these breaks lookups, sorts, and dedupes silently — no error message, just a wrong answer.

Method 1: The TRIM formula (works for regular whitespace)

Excel's built-in =TRIM() removes leading and trailing spaces and collapses runs of internal spaces into single spaces. So =TRIM(" Acme Co ") returns Acme Co.

=TRIM(A2)

Workflow:

1. Insert a helper column to the right of your text column.
2. Drop =TRIM(A2) in the first row of the helper.
3. Fill down to match your data.
4. Copy the helper column.
5. Paste Special > Values back over the original column.
6. Delete the helper.

The catch: TRIM does NOT remove non-breaking spaces. If your data came from a web page or a Word doc, TRIM will leave the Chr(160) characters intact and your lookup mystery continues. To handle non-breaking spaces with formulas, you need:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Now you've got a nested formula and you need to remember the SUBSTITUTE wrapper. Workable, but easy to forget.

Method 2: Find and Replace (manual, but flexible)

Excel's Find and Replace can handle non-breaking spaces if you know the keyboard trick.

1. Press Ctrl + H to open Find and Replace.
2. In "Find what", type the special character. For non-breaking space on Windows, hold Alt and type 0160 on the numeric keypad. (On Mac, copy a non-breaking space from your data first.)
3. Leave "Replace with" blank.
4. Click Replace All.

Then run Ctrl + H again with a regular space in "Find what" and a regular space in "Replace with" to collapse double spaces (this trick uses Excel's Replace All to apply repeatedly when you click multiple times until "0 replacements" comes back).

This works but it's three or four passes minimum, and the Alt-0160 trick fails on most laptop keyboards without a numeric keypad.

Method 3: The free VBA macro (one click, all four cases)

If you clean whitespace more than once, the macro is the right tool. Download Trim Whitespace From All Cells. It's a free .xlsm with one macro.

How to use it:

1. Select the cells you want to clean.
2. Press Alt + F8, pick TrimWhitespaceAllCells, click Run.
3. Done. Leading, trailing, double-internal, and non-breaking spaces are all removed.

The macro handles all four whitespace categories in one pass. The result is plain values, not formulas, so the cleanup survives copy-paste, CSV export, and re-opening the file.

Comparison: which method to use

Method	Handles regular spaces	Handles non-breaking spaces	Result type	Best for
=TRIM() formula	Yes	No	Formula	One-time cleanup, simple data
Find and Replace	Yes (with passes)	Yes (with Alt+0160 trick)	Plain values	Manual cleanup, no recurring need
VBA macro	Yes	Yes	Plain values	Recurring cleanup or messy data

Common scenario: cleaning a CSV import

You imported a CSV. SUMIF returns 0. VLOOKUP returns #N/A. The numbers and lookups should work. The first thing to suspect is whitespace.

1. Run the Trim Whitespace macro on the entire sheet.
2. If lookups still fail, check whether numbers are stored as text. Run Convert Text to Numbers.
3. If lookups still fail, the values legitimately don't match. Time for a manual eyeball.

In this order, 90% of "it should match but doesn't" bugs disappear after step 1 or step 2.

Frequently asked questions

Does TRIM remove tabs and newlines?

No. =TRIM() only handles standard ASCII spaces. Tabs (Chr(9)) and newlines (Chr(10), Chr(13)) need a CLEAN() wrapper: =TRIM(CLEAN(A2)). The macro handles tabs and newlines automatically along with spaces and non-breaking spaces.

Will trimming whitespace change my numbers?

No. The macro and TRIM() only modify text cells. Numbers and dates are skipped. So a column with 123 next to a name with trailing space gets only the name cleaned; the number stays as is.

What if I want to keep some whitespace, like a tab inside a cell?

Use Find and Replace instead of the macro. The macro is opinionated: it strips all the whitespace categories that cause lookup bugs. If you have tabs inside cells that you want to keep, Find/Replace gives you precise control over which characters get touched.

Why does my SUMIF still return 0 after trimming?

Whitespace is one possible cause; the other common cause is numbers stored as text. After trimming, run Convert Text to Numbers on the same column. If SUMIF still returns 0 after both, the values genuinely don't match the criteria you specified.

What to do next

If you're seeing one or two stuck cells, fix them with =TRIM(). If the whole column is dirty (CSV import, web copy-paste), run the Trim Whitespace From All Cells macro and move on. Either way, the next likely follow-up is checking for duplicates or text-stored numbers hiding in the same data.