Apostrophe Cleaner: Fix the Invisible Prefix That Breaks Your Numbers

TL;DR: You import data from a PDF converter or web report and your numbers won’t sum. They look like numbers, they’re right-aligned, but =SUM() returns zero and VLOOKUP returns #N/A. The culprit: an invisible leading apostrophe that forces Excel to treat every value as text. This macro finds every apostrophe-prefixed cell on every sheet, strips the prefix, and converts each cell to its natural type — numbers become numbers, dates become dates. It tells you exactly how many cells were cleaned per sheet.

The Problem

You’re preparing the Henderson 1120S workpaper. The client sent their depreciation schedule as a PDF. You run it through your firm’s PDF-to-Excel converter and drop the output into your workbook. The numbers look fine — account codes, cost basis, accumulated depreciation, all perfectly aligned. But when you write =SUM(D2:D50), the result is zero. You click a cell. The formula bar shows ' 250,000 — an apostrophe, two spaces, and the number. It’s not a number. It’s text that looks like a number.

You find 200 more cells with the same invisible prefix. You click each one, press F2, hit Home, delete the apostrophe, press Enter. Repeat 200 times. Halfway through you realize the client’s trial balance — another PDF conversion — has the same problem. And the bank statement export. And the state apportionment data the client’s bookkeeper emailed as a “clean” Excel file.

Excel gives you zero tools to find or fix these apostrophes in bulk. There’s no “Remove All Apostrophes” button. The green triangle warning in the corner is easy to miss on a sheet with 500 rows. The Error Checking dropdown can convert them one column at a time — if you notice them. But the apostrophe itself is invisible. The only way to see it is to click into each cell and look at the formula bar.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with data imported from external sources — PDF converters, web scrapes, legacy systems, or client-provided files

What the macro does:

Scans every cell on every sheet (or just the active sheet — your choice) for the invisible apostrophe prefix (cell.PrefixCharacter = "'")
Strips the apostrophe by reassigning the cell’s value to itself
Converts text-numbers to actual numbers, text-dates to actual dates
Reports the count of cleaned cells per sheet so you know exactly where the problems were
Skips cells that don’t have the apostrophe prefix — no unnecessary writes

Limitations:

Only detects the ' apostrophe prefix character, not other text-formatting artifacts like leading spaces or non-breaking spaces. Run the Leading/Trailing Space Stripper for those
cell.Value = cell.Value may convert a genuinely text cell like '00123 to the number 123 (losing leading zeros). The macro reports what it changed so you can spot-check account-code columns
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
Cells containing only an apostrophe followed by nothing (empty text cells) are converted to truly blank cells — this is intentional

#The Macro

Option Explicit

Sub CleanApostrophes()
    ' ── Apostrophe Cleaner ─────────────────────────────
    ' Finds every cell prefixed with a leading apostrophe
    ' (the ' character that forces text formatting) and
    ' strips it. Converts text-numbers to actual numbers
    ' and text-dates to actual dates.
    '
    ' One prompt: all sheets or active sheet only.
    ' Zero code editing required.
    ' ────────────────────────────────────────────────────

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

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

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

    ' ── User prompt ────────────────────────────────────
    scopeMsg = MsgBox("Clean 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)

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

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

        sheetCleaned = 0

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

        For Each cell In ws.UsedRange.Cells
            If cell.PrefixCharacter = "'" Then
                cell.Value = cell.Value
                sheetCleaned = sheetCleaned + 1
            End If
        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 apostrophe-prefixed cells found.", _
               vbInformation, "Apostrophe Cleaner"
    Else
        MsgBox "Cleaned " & totalCleaned & " apostrophe" & _
               IIf(totalCleaned = 1, "", "s") & "." & _
               vbCrLf & vbCrLf & report, _
               vbInformation, "Apostrophe Cleaner"
    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

#One prompt, zero code editing

The macro asks exactly one question: all sheets or just the active sheet? Three choices — Yes (all), No (active only), Cancel (stop). That’s it. No InputBoxes that make you type anything. No constants at the top of the code. No configuration arrays to edit.

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

Why offer active-sheet-only? Because sometimes you know which sheet has the problem — you just converted a single PDF and dropped it onto one tab. Running the full workbook scan would be wasteful and would also touch sheets (like your formula-driven summary tabs) that don’t need cleaning. The scope prompt gives you control without making you edit code.

#`PrefixCharacter` — the invisible property you’ve never heard of

Excel stores a leading apostrophe as a cell property called PrefixCharacter, not as part of the cell’s value. When you type '250000 into a cell, Excel stores 250000 as the value but sets PrefixCharacter to '. The value is text, not a number. The apostrophe itself is metadata — it tells Excel “treat this cell as text no matter what.”

This is why =SUBSTITUTE(A1, "'", "") doesn’t work — there’s no apostrophe in the cell’s value to substitute. And why =VALUE(A1) fails — Excel can’t convert a text string that it already knows is “really” a number because the PrefixCharacter flag overrides the type inference.

For Each cell In ws.UsedRange.Cells
    If cell.PrefixCharacter = "'" Then
        cell.Value = cell.Value
        sheetCleaned = sheetCleaned + 1
    End If
Next cell

The fix is elegantly simple: cell.Value = cell.Value. This reassigns the cell’s value to itself, which forces Excel to re-evaluate the type. If the underlying text is 250000, Excel now sees it as a number. If it’s 12/31/2026, Excel now sees it as a date. The PrefixCharacter is cleared as a side effect of the reassignment.

#The leading-zero trap — and why the report matters

cell.Value = cell.Value has one meaningful side effect: leading zeros are stripped. If a cell contains '00123 (an account code with a leading zero), after the macro it becomes the number 123. The leading zeros are gone because numbers don’t have leading zeros.

This is NOT a bug — it’s the expected behavior of converting text to numbers. But it matters for account codes, ZIP codes, and any identifier where the leading zero carries meaning. The macro handles this by reporting exactly what it changed, per sheet:

Cleaned 342 apostrophes.

  • Depreciation Import — 156 cell(s)
  • TB PDF — 98 cell(s)
  • Bank Statements — 64 cell(s)
  • State Data — 24 cell(s)

If you see "Account Codes — 47 cell(s)" and your account codes all start with zeros, you know to spot-check that column. The report is your audit trail, not just a notification.

#Why skip protected sheets instead of erroring out

If a sheet is protected, the macro can’t write to it. Instead of crashing mid-scan with a runtime error, it adds a note to the report and moves on:

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

The final message box might look like:

Cleaned 298 apostrophes.

  • Depreciation Import — 156 cell(s)
  • TB PDF — 98 cell(s)
  • Bank Statements — 44 cell(s)
  • Client Schedule — SKIPPED (protected)

This tells you there’s likely apostrophe-prefixed data on the Client Schedule tab that needs cleaning, but you need to unprotect the sheet first. It’s a reminder, not a blocker — and it doesn’t prevent the macro from cleaning the three unprotected sheets.

#The calculation toggle matters here

Unlike the filter-remover and hyperlink-stripper (which don’t change cell values), this macro modifies cell contents. When cell.Value = cell.Value converts a text-number to an actual number, every formula that references that cell recalculates. On a sheet with 200 cleaned cells and 50 SUMIF/VLOOKUP formulas, that’s 10,000 recalculations.

Application.Calculation = xlCalculationManual

Suspend calculation at the top, do all the cleaning in one pass, restore at the bottom. Excel recalculates once instead of 10,000 times. The difference on a large workbook is the macro finishing in 2 seconds vs. 45 seconds.

#Where apostrophes come from (and why you keep seeing them)

The leading apostrophe prefix is not something people type intentionally. It appears automatically when Excel imports data that it can’t classify:

PDF-to-Excel converters — These are the #1 source. A PDF has no concept of “number” vs. “text” — every value is a glyph on a page. The converter takes a best guess and, when unsure, prefixes with ' to play it safe. Numbers with commas (' 1,500,000), numbers with parentheses (' (250)), and dates in ambiguous formats (' 01/02/2026 — is that Jan 2 or Feb 1?) all get the apostrophe treatment.
Web scrapes and HTML exports — Tables copied from websites often include non-breaking spaces, currency symbols, and formatting characters that Excel doesn’t recognize. The apostrophe prefix is Excel’s “I give up — keep it as text” fallback.
Legacy systems and mainframe dumps — Fixed-width text files from systems built in the 1980s often use leading zeros, packed decimals, and non-standard delimiters. Excel’s Text Import Wizard gives you the option to specify column types, but if the file extension is .xlsx or .csv instead of .txt, the Wizard never appears and Excel applies the apostrophe prefix to anything it can’t parse.
Copy-paste from other applications — Pasting a table from a web browser, a PDF viewer, or even another spreadsheet application can introduce hidden formatting characters that trigger the apostrophe prefix.

#Adapt It

Only process the active sheet — no prompt. If you always work one sheet at a time and don’t want the scope prompt, remove the MsgBox and hardcode the value:

processAll = False

Replace the entire scopeMsg block with that one line. The macro silently processes only the active sheet.

Run on the active workbook instead of the macro workbook. Store this macro in your Personal Macro Workbook (PERSONAL.XLSB) and change ThisWorkbook.Worksheets to ActiveWorkbook.Worksheets:

For Each ws In ActiveWorkbook.Worksheets

If no workbook is open, the error handler catches it and shows a friendly message.

Skip certain sheets from cleaning. If you have sheets with intentional apostrophes (rare, but possible in custom templates), add a filter:

If ws.Name Like "Template*" Or ws.Name = "Admin" Then GoTo NextSheet

Put this right after the For Each ws line, before the protection check.

Also run the space stripper in the same pass. If your imported data has both apostrophe prefixes AND leading/trailing spaces, chain the cleaning by adding the space-trimming logic before the apostrophe check:

For Each cell In ws.UsedRange.Cells
    ' Trim spaces first (skip numbers)
    If Not IsEmpty(cell) And Not IsNumeric(cell.Value) Then
        Dim newVal As String
        newVal = VBA.Trim(CStr(cell.Value & ""))
        If newVal <> CStr(cell.Value & "") Then
            cell.Value = newVal
        End If
    End If

    ' Then strip apostrophe prefix
    If cell.PrefixCharacter = "'" Then
        cell.Value = cell.Value
        sheetCleaned = sheetCleaned + 1
    End If
Next cell

This handles the common PDF-conversion case where cells have both ' 250,000 (apostrophe + spaces + number). The VBA.Trim pass removes the spaces; the PrefixCharacter check removes the apostrophe.

Assign a keyboard shortcut. In the Macro dialog (Alt+F8), select CleanApostrophes, click Options, and set a shortcut like Ctrl+Shift+A. Two keystrokes every time you import data from an external source.

Store it in your Personal Macro Workbook. The apostrophe cleaner is most useful when it’s available in every workbook you open:

Press Alt+F11 to open the VBA editor
In the Project Explorer, find VBAProject (PERSONAL.XLSB)
If you don’t have one: Record a dummy macro (View → Macros → Record), store it in “Personal Macro Workbook,” then stop recording
Insert a Module and paste the code
Now CleanApostrophes is available in every workbook you open

See also: Text-to-Numbers Converter — handles the sibling problem: cells that contain numbers stored as text due to number format settings, not apostrophe prefixes. Leading/Trailing Space Stripper — the third leg of the import cleanup stool. Run all three on any file that came from outside Excel.

Download the sample workbook (7 sheets — 5 with apostrophe-prefixed cells from various import sources, 2 clean, plus Instructions)

xlsx 28 KB