VBA Trim in Excel — Why It Doesn't Remove Your Spaces (and the Chr(160) Fix)

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-10

TL;DR — VBA's Trim removes spaces from the two ends of a string and nothing else. It does not collapse double spaces inside the text, and it does not remove the non-breaking space (Chr(160)) that web pages and PDFs paste in. That second fact is why "Trim didn't work" is one of the most-searched VBA complaints.

Sub TrimDemo()
    Dim s As String
    s = "   Acme  Corp   "                 ' ends + a DOUBLE space in the middle

    Debug.Print "[" & Trim(s) & "]"        ' [Acme  Corp]  <- ends gone, inner double space STAYS
    Debug.Print "[" & LTrim(s) & "]"       ' [Acme  Corp   ]  left side only
    Debug.Print "[" & RTrim(s) & "]"       ' [   Acme  Corp]  right side only
End Sub

To also squeeze the runs of spaces inside the text down to one, you need the worksheet function, not the VBA keyword:

Debug.Print Application.WorksheetFunction.Trim("   Acme  Corp   ")   ' "Acme Corp"

The mental model: Trim is a haircut for the ends, not a shampoo

Trim tidies the edges. It walks in from the left until it hits a non-space, walks in from the right until it hits a non-space, and returns what is between. Everything in the middle is left exactly as it was. That is the whole behaviour, and it explains every surprise people hit.

Once you hold that picture, the family makes sense at a glance: LTrim is the left edge only, RTrim is the right edge only, Trim is both. None of them care about the interior. So when your problem is "this user typed two spaces between the first and last name," Trim is simply the wrong tool — reach for WorksheetFunction.Trim, which is a different function with the same name that also collapses internal runs.

The one rule: VBA Trim only removes the ASCII space, Chr(32)

This is the rule that turns a baffling bug into a one-line fix:

Trim recognises exactly one character as "space to remove": the ordinary ASCII space, Chr(32). Any other whitespace-looking character — a tab, a line break, and above all the non-breaking space Chr(160) — is, to Trim, just a normal character it must keep.

Here is the trap in its natural habitat. You copy a value from a web page, an HTML email, or a PDF into Excel. Visually it ends in a space. Your Trim runs, the cell still won't match your lookup, and you lose an hour:

Dim raw As String
raw = "Acme Corp" & Chr(160)             ' a trailing NON-BREAKING space, from a web paste
Debug.Print Len(Trim(raw))               ' 10, not 9 — Trim removed nothing
Debug.Print (Trim(raw) = "Acme Corp")    ' False!  the values look identical but aren't

The fix is not a cleverer Trim. It is to turn the Chr(160) into a real space first, then trim:

Function CleanText(ByVal s As String) As String
    s = Replace(s, Chr(160), " ")                          ' non-breaking space -> normal space
    s = Application.WorksheetFunction.Clean(s)             ' strip control chars Chr(0)-Chr(31)
    CleanText = Application.WorksheetFunction.Trim(s)      ' collapse inner runs + trim both ends
End Function

Debug.Print Len(CleanText("Acme Corp" & Chr(160)))        ' 9 — finally clean

Clean deserves a mention here: it removes the non-printable control characters (carriage returns, line feeds, tabs in the 0–31 range) that sneak in from exports — but note it does not remove Chr(160), because 160 is above that range. That is why a real-world cleanup is the trio above, in that order, not any single function.

The second trap: Trim vs Trim$

There are two Trims. Bare Trim is the Variant version; Trim$ is the String-typed twin. Two practical differences:

• Trim$ is marginally faster and returns a String directly, which matters in a tight loop over thousands of cells.
• Trim$ raises an error on Null, while Trim quietly passes Null through. When you read from a Variant that might be Null (a database field, an empty Range.Value), the bare Trim is the forgiving one.

My habit: Trim$ inside loops where I control the types, bare Trim at the boundary where a value might be Null. Knowing which you're holding prevents a surprise Invalid use of Null.

When to use which

The opinion: don't chain Trims — normalize once at the boundary

The anti-pattern I see most is Trim sprinkled defensively all over a macro — Trim(rng.Value) here, Trim(parts(i)) there — in the hope that one of them finally makes the comparison work. It never does for Chr(160), because none of those Trims touch it.

Cleaning text is a boundary concern, not a per-line one. The data comes in dirty at exactly one place — the import, the paste, the read — so clean it there, once, with a single CleanText function like the one above, and trust it everywhere downstream. A macro that normalizes at the boundary is readable and correct; a macro with forty scattered Trims is a macro whose author was guessing. If your comparisons still fail after a real clean, the difference isn't whitespace at all — it's a Chr(160), a casing mismatch (see CStr / type conversion), or a number stored as text.

Common Trim mistakes (and the fix)

When the cleanup outweighs the point — describe the job instead

You didn't set out to study whitespace. You set out to match this week's export against your master list, and the names won't line up because half of them arrived wrapped in Chr(160). By the time you've written the clean function, looped the column, and chased the one row that still won't match, the plumbing has eaten the afternoon. ExcelMaster Agent lets you say the goal in plain English — "clean the whitespace and hidden characters in column B, then match it to the master sheet" — and it writes Python that handles Chr(160), control characters, and casing, backing up your workbook first. Try it free →

Related guides

• VBA Format — Dates, Numbers & Why Formatting Breaks Your Math
• VBA CStr, CDate & Val — Convert Text to Numbers & Dates the Right Way
• VBA InStr — Find Text Inside a String
• VBA Split — Turn One String Into an Array
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

Why is my VBA Trim not working? Almost always because the "space" isn't a space. Trim only removes the ASCII space Chr(32). Text pasted from web pages, HTML emails, or PDFs frequently ends in a non-breaking space Chr(160), which Trim keeps. Replace it first: Trim(Replace(s, Chr(160), " ")).

Does VBA Trim remove double spaces inside a string? No. The VBA Trim keyword only removes spaces from the two ends. To collapse internal runs of spaces down to a single space, use the worksheet function instead: Application.WorksheetFunction.Trim(s).

What is the difference between Trim and Trim$ in VBA? Trim returns a Variant and passes Null through unchanged; Trim$ returns a String directly, is slightly faster, and raises an error on Null. Use Trim$ in tight loops where types are known, and bare Trim where a value might be Null.

How do I remove non-breaking spaces (Chr 160) in VBA? Replace them with a normal space, then trim: Application.WorksheetFunction.Trim(Replace(s, Chr(160), " ")). Add WorksheetFunction.Clean(s) to strip control characters like line breaks and tabs in the same pass.

What does VBA Clean do, and is it the same as Trim? No. Clean removes non-printable control characters (codes 0–31, like line breaks and tabs); Trim removes spaces. They solve different problems and are often used together. Note that Clean does not remove Chr(160), since 160 is outside the control-character range.