Text-to-Numbers Converter: Fix Every Imported Number That Won't Sum

TL;DR: Your ERP dumps trial balances as text. The bank CSV has dollar signs and commas Excel treats as strings. SUMIF returns zero, VLOOKUP can’t match, and your pivot table says there’s no data. This macro scans every sheet, strips dollar signs, commas, spaces, and parentheses, then converts every text-number to a real number. One InputBox chooses all sheets or just the active one. The green triangles are gone before you finish your coffee.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with text-formatted numbers — CSV exports, ERP dumps, bank file imports, or any data where numbers came in as text strings

What the macro does:

• Finds every cell on the target sheets where the value is stored as text but looks like a number
• Strips dollar signs ($), commas (,), spaces, and parentheses — the formatting characters that fool IsNumeric
• Converts all matches to actual numeric values with CDbl()
• Reports the total count and number of sheets affected

What the macro does NOT convert:

• Genuine text (“N/A”, “See Note 4”, “Management Estimate”) — the helpers catch these
• Dates stored as text (“12/31/2026”) — IsNumeric naturally rejects these
• Mixed content like “Approx 500” or “Unit 12B” — detected and skipped
• Account codes with letters (“11010A”) — mixed detection catches them
• Formula results — only constant cells are checked. If a formula returns a text-number, you need a different approach (see Adapt It)

Limitations:

• Only scans cells within UsedRange — data outside that range is ignored
• Does not convert text-percentages to decimal equivalents. A cell containing "42.5%" is skipped because % is not stripped (percentages need a different conversion: divide by 100 after stipping the sign)
• If a sheet has no text constants, it’s silently skipped — no wasted cycles
• SpecialCells(xlCellTypeConstants, xlTextValues) raises an error if there are no text constants on the sheet. The macro handles this with On Error Resume Next around that call

#The Macro

Option Explicit

Sub ConvertTextToNumbers()
    ' ── Text-to-Numbers Converter ──────────────────────
    ' Scans every sheet (or active sheet only) for text-
    ' formatted numbers and converts them to actual
    ' numeric values. Strips $, commas, spaces, and
    ' parentheses before testing. Skips mixed text-and-
    ' digit entries like "Approx 500".
    '
    ' One InputBox: all sheets or active sheet only.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim cell As Range
    Dim rng As Range
    Dim scopeAll As Boolean
    Dim answer As String
    Dim converted As Long
    Dim sheetConverted As Long
    Dim sheetsAffected As Long

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

    ' ── Scope: all sheets or active sheet only? ────────
    answer = InputBox("Convert text-numbers on ALL sheets?" & vbCrLf & _
                      "Type YES or press Enter for all sheets." & vbCrLf & _
                      "Type NO for the active sheet only.", _
                      "Text to Numbers", "YES")
    If StrPtr(answer) = 0 Then GoTo CleanUp
    scopeAll = (UCase(Trim(answer)) <> "NO" And _
                UCase(Trim(answer)) <> "N")

    converted = 0
    sheetsAffected = 0

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

        ' Find every text constant on this sheet. The
        ' xlTextValues argument filters to cells that
        ' contain text, not formulas or numbers.
        On Error Resume Next
        Set rng = Nothing
        Set rng = ws.UsedRange.SpecialCells( _
            xlCellTypeConstants, xlTextValues)
        On Error GoTo CleanUp

        If rng Is Nothing Then GoTo NextSheet

        sheetConverted = 0
        For Each cell In rng
            If VarType(cell.Value) = vbString Then
                If IsNumberStoredAsText(CStr(cell.Value)) Then
                    cell.Value = CDbl(StripFormatting(CStr(cell.Value)))
                    sheetConverted = sheetConverted + 1
                End If
            End If
        Next cell

        If sheetConverted > 0 Then
            converted = converted + sheetConverted
            sheetsAffected = sheetsAffected + 1
        End If

NextSheet:
    Next ws

    ' ── Report results ─────────────────────────────────
    If converted = 0 Then
        MsgBox "No text-formatted numbers found.", _
               vbInformation, "Text to Numbers"
    Else
        MsgBox converted & " text-number(s) converted " & _
               "across " & sheetsAffected & " sheet(s).", _
               vbInformation, "Text to Numbers"
    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

' ── Helper: Is this string a number stored as text? ────
Private Function IsNumberStoredAsText(val As String) As Boolean
    Dim s As String
    s = Trim(val)
    If Len(s) = 0 Then Exit Function

    ' Skip entries that mix meaningful text and digits
    ' (e.g., "Approx 500", "Unit 12B", "11010A").
    If HasMixedContent(s) Then Exit Function

    ' Strip formatting characters and test what's left
    IsNumberStoredAsText = IsNumeric(StripFormatting(s))
End Function

' ── Helper: Strip $ , space and handle ( ) ─────────────
Private Function StripFormatting(val As String) As String
    Dim s As String
    s = val

    s = Replace(s, "$", "")
    s = Replace(s, ",", "")
    s = Replace(s, " ", "")

    ' Accounting-style negatives: (1,234.56) → -1234.56
    If Left(s, 1) = "(" And Right(s, 1) = ")" Then
        s = "-" & Mid(s, 2, Len(s) - 2)
    End If

    StripFormatting = s
End Function

' ── Helper: True if the string contains both letters ───
'            and digits (i.e., mixed content)            '
Private Function HasMixedContent(val As String) As Boolean
    Dim i As Long
    Dim hasLetter As Boolean, hasDigit As Boolean

    For i = 1 To Len(val)
        Select Case Mid(val, i, 1)
            Case "0" To "9": hasDigit = True
            Case "A" To "Z", "a" To "z": hasLetter = True
        End Select
    Next i

    HasMixedContent = (hasLetter And hasDigit)
End Function

#How It Works

#Why SpecialCells instead of a full cell-by-cell scan

A naive approach would loop over every cell in UsedRange — all 17 billion cells in a full worksheet. That’s impossibly slow. The macro uses SpecialCells(xlCellTypeConstants, xlTextValues) to ask Excel for only the cells that are both (a) constants, not formulas, and (b) text values, not numbers. This reduces a 500-row × 20-column sheet from 10,000 cells to maybe 50 candidates.

The SpecialCells call raises an error if no cells match. The macro wraps it in On Error Resume Next so a sheet with zero text constants is silently skipped instead of crashing:

On Error Resume Next
Set rng = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo CleanUp
If rng Is Nothing Then GoTo NextSheet

#The formatting stripper — why commas and dollar signs break IsNumeric

VBA’s IsNumeric("1,234.56") returns False. The comma looks like a list separator in some locales. IsNumeric("$500") also returns False. These are the exact formatting characters that CSV exports and ERP dumps produce, and they’re the reason a simple If IsNumeric(cell.Value) Then approach silently fails.

The StripFormatting helper removes $, ,, and spaces, then handles parentheses for accounting-style negative numbers:

s = Replace(s, "$", "")
s = Replace(s, ",", "")
s = Replace(s, " ", "")
If Left(s, 1) = "(" And Right(s, 1) = ")" Then
    s = "-" & Mid(s, 2, Len(s) - 2)
End If

$1,234.56 → 1234.56 → IsNumeric returns True → CDbl converts to 1234.56. The underlying value is now a proper double, and all your SUMIF and VLOOKUP formulas start working.

#Mixed content detection — why “Approx 500” must be skipped

The hardest case is a cell that contains both text and a number: "Approx 500", "Unit 12B", "See Note 4". These look partially numeric but converting them would be wrong — you’d lose the context.

The HasMixedContent helper checks whether the string contains both letters (A-Z, a-z) and digits (0-9):

For i = 1 To Len(val)
    Select Case Mid(val, i, 1)
        Case "0" To "9": hasDigit = True
        Case "A" To "Z", "a" To "z": hasLetter = True
    End Select
Next i
HasMixedContent = (hasLetter And hasDigit)

This catches three classes of non-numeric text:

• Mixed text+number: "Approx 500" — has both letters and digits, skipped
• Alphanumeric codes: "11010A" — account code with a suffix letter, skipped
• Pure text: "N/A" — has letters but no digits, passes mixed-content but fails IsNumeric after stripping (nothing to strip), skipped
• Pure number text: "1,234.56" — no letters, only digits and punctuation, passes mixed-content and passes IsNumeric after stripping → converted

#Why CDbl instead of cell.Value = cell.Value

A common Excel trick for text-numbers is cell.Value = cell.Value — you assign the cell’s own value back to itself, and Excel auto-converts it. This works on cells where the value is a pure number string like "1234" but fails on "$1,234.56" because Excel tries to parse the dollar sign and comma and gives up.

CDbl is explicit. After StripFormatting removes the formatting characters, the result is a string like "1234.56" which CDbl converts to the numeric 1234.56. There’s no ambiguity and no locale-dependent parsing.

One tradeoff: CDbl can overflow on very large numbers (>1E308) and lose precision beyond 15 significant digits. For tax workpapers where numbers top out in the billions, this is irrelevant. If you regularly work with values in the trillions (national debt schedules, GDP calculations), use CDec instead — it supports 28-29 significant digits and won’t overflow on realistic government-scale numbers.

#The InputBox default is “YES” — the safe, fast path

The InputBox pre-fills with "YES" and accepts Enter as confirmation. A preparer who just wants to convert everything presses Enter once and the macro runs on all sheets. This is the 95% use case — you import a file, everything’s broken, you want everything fixed.

The “NO” path is for the 5% case: you’ve built a multi-sheet workpaper and only one sheet has imported data. You don’t want the macro touching clean sheets unnecessarily. The scopeAll flag gates the loop:

If Not scopeAll Then
    If ws.Name <> ActiveSheet.Name Then GoTo NextSheet
End If

#ScreenUpdating AND Calculation both toggle off

Every cell conversion triggers a recalc. For 342 text-numbers across 5 sheets with cross-sheet formulas, that’s 342 recalculation cascades. The Calculation = xlCalculationManual toggle suppresses them all until the end, then one final recalc runs on cleanup.

ScreenUpdating = False prevents Excel from redrawing after each cell flip — the visible “flicker” as left-aligned snaps to right-aligned 342 times. With it off, the macro finishes in under a second.

#The message box tells you what changed

A silent conversion is worse than no conversion — you don’t know what was changed or whether it worked. The MsgBox shows the count:

342 text-number(s) converted across 5 sheet(s).

If you expected 342 and got 280, 62 cells had mixed content or were genuinely text. If you expected 5 sheets and got 3, two sheets had no text-numbers to begin with. The message box is your audit trail.

#Adapt It