Leading/Trailing Space Stripper: Fix the #1 Reason Your VLOOKUP Returns #N/A

TL;DR: Your VLOOKUP returns #N/A even though the account code is right there on the sheet. The problem is invisible: a trailing space from the ERP export, or a leading space from the client’s QuickBooks dump. This macro scans every sheet, trims leading and trailing spaces from every cell, and optionally squashes internal double-spaces. It tells you exactly how many cells were cleaned on each tab. One click, zero formulas written to the sheet.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with data imported from external systems — ERP exports, bank feeds, client-provided CSVs, or PDF conversions

What the macro does:

• Trims leading and trailing spaces from every cell on every sheet (or just the active sheet — your choice)
• Optionally squashes multiple consecutive internal spaces into single spaces
• Reports the count of modified cells per sheet so you know where the problems were
• Skips cells that are already clean — no unnecessary writes

Limitations:

• Internal double-space reduction is optional — you choose during the prompt. If you decline, cells like "Fixed Assets" (three spaces) stay as-is
• Does not trim cells containing only spaces to empty — those were likely intentional placeholders. You’ll see them counted as “skipped”
• Works on ThisWorkbook — the workbook containing the macro. Store it in your Personal Macro Workbook (PERSONAL.XLSB) if you want to run it on any open file
• Protected sheets are skipped with a note in the final count
• Non-breaking spaces (character 160, the   of the web world) require a different approach. This macro handles standard ASCII spaces (character 32)

#The Macro

Option Explicit

Sub TrimAllSpaces()
    ' ── Trim All Spaces ────────────────────────────────
    ' Trims leading and trailing spaces from every cell
    ' on every sheet (or just the active sheet).
    ' Optionally squashes internal double-spaces.
    ' Reports exactly how many cells were cleaned
    ' per sheet.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim cell As Range
    Dim scopeMsg As VbMsgBoxResult
    Dim doubleSpaceMsg As VbMsgBoxResult
    Dim totalCleaned As Long
    Dim sheetCleaned As Long
    Dim report As String
    Dim newVal As String
    Dim oldVal As String
    Dim processAll As Boolean
    Dim fixDoubles As Boolean

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── User prompts ───────────────────────────────────
    scopeMsg = MsgBox("Trim ALL sheets?" & vbCrLf & _
               "Yes = All sheets" & vbCrLf & _
               "No = Active sheet only" & vbCrLf & _
               "Cancel = Stop", _
               vbYesNoCancel + vbQuestion, "Scope")
    If scopeMsg = vbCancel Then GoTo CleanUp
    processAll = (scopeMsg = vbYes)

    doubleSpaceMsg = MsgBox("Also fix internal double-spaces?" & vbCrLf & _
                    "(e.g., ""Fixed   Assets"" becomes " & _
                    """Fixed Assets"")", _
                    vbYesNo + vbQuestion, "Double Spaces")
    fixDoubles = (doubleSpaceMsg = vbYes)

    ' ── Process sheets ─────────────────────────────────
    totalCleaned = 0
    report = ""

    For Each ws In ThisWorkbook.Worksheets
        If Not processAll And ws.Name <> ActiveSheet.Name Then GoTo NextSheet

        sheetCleaned = 0

        ' Skip protected sheets
        If ws.ProtectContents Then
            report = report & "  • " & ws.Name & _
                     " — SKIPPED (protected)" & vbCrLf
            GoTo NextSheet
        End If

        For Each cell In ws.UsedRange.Cells
            If Not IsEmpty(cell) And Not IsNumeric(cell.Value) Then
                oldVal = CStr(cell.Value & "")
                If oldVal = "" Then GoTo NextCell

                ' Step 1: Trim leading/trailing spaces
                newVal = VBA.Trim(oldVal)

                ' Step 2: Optionally fix internal double-spaces
                If fixDoubles Then
                    Do While InStr(newVal, "  ") > 0
                        newVal = Replace(newVal, "  ", " ")
                    Loop
                End If

                ' Only write if something changed
                If newVal <> oldVal Then
                    cell.Value = newVal
                    sheetCleaned = sheetCleaned + 1
                End If
            End If
NextCell:
        Next cell

        If sheetCleaned > 0 Then
            report = report & "  • " & ws.Name & " — " & _
                     sheetCleaned & " cell(s)" & vbCrLf
            totalCleaned = totalCleaned + sheetCleaned
        End If
NextSheet:
    Next ws

    ' ── Report results ─────────────────────────────────
    If totalCleaned = 0 Then
        MsgBox "No cells with leading/trailing spaces found.", _
               vbInformation, "Trim All Spaces"
    Else
        MsgBox "Trimmed " & totalCleaned & " cell(s)." & vbCrLf & _
               vbCrLf & report, vbInformation, "Trim All Spaces"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Two prompts, no code editing

The macro asks two questions before it does anything. This is the “zero code editing” philosophy in practice — you never open the VBA editor to configure the macro. You answer two MsgBox prompts and the macro figures out the rest:

1. Scope: All sheets or just the active sheet? Three choices — Yes (all), No (active only), Cancel (stop). The most common workflow is “Yes” for inherited files where every sheet is suspect.
2. Internal double-spaces: Fix them or leave them? Most of the time you want this on — "Fixed Assets" (three spaces between words) is a real problem from concatenated ERP exports. But it’s optional because double-spaces in descriptions or notes might be intentional formatting.

No InputBoxes that make you type anything. No constants at the top of the code. Two clicks and the macro runs.

scopeMsg = MsgBox("Trim ALL sheets?" & vbCrLf & _
           "Yes = All sheets" & vbCrLf & _
           "No = Active sheet only" & vbCrLf & _
           "Cancel = Stop", _
           vbYesNoCancel + vbQuestion, "Scope")

#VBA.Trim vs. WorksheetFunction.Trim — the right tool for the job

VBA gives you two TRIM functions, and they’re not the same thing:

• VBA.Trim(str) — removes only leading and trailing spaces. Internal spaces are left untouched. This is the fast, safe default.
• WorksheetFunction.Trim(str) — removes leading/trailing AND squashes internal multi-spaces to singles. This is what Excel’s =TRIM() formula does.

This macro uses VBA.Trim for the first pass because it’s faster and doesn’t touch the middle of a string unless you explicitly ask it to. If the user says “Yes” to internal double-spaces, the macro does a second pass with a simple Replace loop:

newVal = VBA.Trim(oldVal)          ' Step 1: edges only, fast

If fixDoubles Then
    Do While InStr(newVal, "  ") > 0
        newVal = Replace(newVal, "  ", " ")
    Loop
End If

The Do While loop is necessary because Replace in one pass can’t handle triple (or more) spaces — "Fixed Assets" becomes "Fixed Assets" after one Replace, then "Fixed Assets" after the second iteration. The loop keeps going until no double-spaces remain.

#Why not just use =TRIM() formulas

You could add a helper column with =TRIM(A2) next to every import column. For a trial balance with 300 rows and 6 columns, that’s 1,800 formulas. For a fixed asset schedule being cross-referenced by VLOOKUPs on three other sheets, you now need to update every reference to point to the helper columns. And if the import is refreshed next month, someone has to remember to drag the formulas down.

This macro does it in-place, once, and the workbook is clean. No helper columns to manage, no formula references to update.

#It tells you exactly where the problems were

The report message shows a per-sheet breakdown:

Trimmed 89 cell(s).

  • TB — 42 cell(s)
  • Fixed Assets — 31 cell(s)
  • JE Log — 16 cell(s)

This is an audit trail, not just a notification. If you see "TB — 42 cells" and you only imported account descriptions, you know 42 descriptions had spaces. If you see "Notes — 0 cells" but expected cleaning there, the data is actually fine and you were wrong about the problem. The per-sheet count lets you verify the macro did what you expected on the sheets you care about.

#Why it skips numbers and blanks

The macro has two guards before it does any work:

If Not IsEmpty(cell) And Not IsNumeric(cell.Value) Then
    oldVal = CStr(cell.Value & "")
    If oldVal = "" Then GoTo NextCell

1. IsEmpty check — truly blank cells are skipped. They don’t have spaces; they have nothing.
2. IsNumeric check — numbers are skipped. A number stored as a number can’t have leading spaces. However, a number stored as text (like the green-triangle imports from the Text-to-Numbers post) will also be skipped here. If you have text-formatted numbers with spaces, run the Text-to-Numbers Converter macro first, then this one.
3. Empty string check — formula results that return "" are skipped. These look blank but contain a zero-length string. They’re not spaces.

The numeric skip is the biggest time-saver. On a sheet with 20,000 rows and 10 columns, 180,000 of the 200,000 cells are probably numbers. Skipping them with a one-line IsNumeric check means the macro processes 20,000 text cells in under a second instead of churning through every cell.

#Protected sheets get a note, not an error

If a sheet is protected, the macro can’t write to it. Instead of crashing, it adds a note to the report:

If ws.ProtectContents Then
    report = report & "  • " & ws.Name & _
             " — SKIPPED (protected)" & vbCrLf
    GoTo NextSheet
End If

The final message box might look like:

Trimmed 67 cell(s).

  • TB — 42 cell(s)
  • Fixed Assets — 25 cell(s)
  • Sched-M1 — SKIPPED (protected)
  • JE Log — 0 cells

This tells you there’s data on Sched-M1 that might need trimming, but you need to unprotect the sheet first. It’s a reminder, not a blocker.

#Adapt It