Trim Whitespace From All Cells

What it does

You imported a CSV. The data looks fine. You build a VLOOKUP. It returns #N/A for half the rows. The cells look identical, but they're not. There's an invisible non-breaking space at the end of one of them, or a tab character snuck in, or someone hit double-space twice. This macro scrubs the invisible junk: leading and trailing spaces, runs of multiple internal spaces, tabs, line feeds, and the dreaded non-breaking space (Chr 160). Excel's built-in TRIM() only handles regular spaces, so it misses most of these.

Real-world example

A high school admin pulls the class roster from the school's information system. The export is a tidy-looking CSV with student names, parent contacts, and emergency numbers. She pastes it into Excel and starts a mail merge for back-to-school night.

Half the merge fields print blank.

She squints at the cells. The names look identical to her template's lookup column. Same spelling. Same case. But VLOOKUP returns #N/A. After 40 minutes of staring, she realizes: the SIS export wraps every value with a non-breaking space, the kind of invisible character you can't see, can't easily search for, and can't normally type. Standard TRIM() doesn't touch it. Find-and-Replace with a regular space doesn't match it.

She finds this macro, selects all the cells in her roster, hits Alt + F8, runs TrimWhitespaceAllCells. Five seconds later: 240 cells cleaned. The VLOOKUP works. The mail merge prints clean.

Realistic time saved: anywhere from 20 minutes to two hours, depending on how long you spend swearing at Excel before you figure out the problem is invisible characters. (For most people, the first time, it's "way more than two hours.") Subsequent CSV imports: 5 seconds.

The annoying truth about non-breaking spaces is that nobody warns you they exist, and once you've been bitten you spot them everywhere. This macro is the fix you wish you'd had three years ago.

How to use

First time? See How to run our tools for the 30-second setup.

1. Paste your data into the workbook.
2. Select the cells you want to clean. (Use Ctrl/Cmd + A for the whole sheet.)
3. Press Alt + F8 / Option + F8, pick TrimWhitespaceAllCells, click Run.
4. The macro reports how many cells were cleaned.

FAQ

Will this break my formulas?

No. The macro skips any cell with a formula. Only literal text values get modified. Numbers, dates, booleans, and formulas are all left alone.

What's a non-breaking space and why does it ruin everything?

It's character code 160 (regular space is 32). It looks identical to a normal space, but Excel treats it as a different character. They're common in copy-pastes from web pages, PDFs, and database exports. They are the single most common cause of "my VLOOKUP should match but doesn't." This macro replaces them with regular spaces.

What about cells with newlines inside them?

Tabs, line feeds, and carriage returns all get replaced with a single space. Then any resulting double spaces are collapsed into one. So a cell with "line one\nline two" becomes "line one line two".

Can I undo it?

Yes. Ctrl/Cmd + Z right after running. The macro performs all changes in a single undoable batch.

Why not just use Excel's TRIM function?

TRIM() handles only regular spaces (Chr 32) and only leading/trailing/internal collapse. It doesn't touch non-breaking spaces, tabs, or line feeds. You'd also need a helper column, then to copy-paste-as-values, then delete the helper. This macro modifies cells in place, no helpers needed.