Why Excel's =SUM() Function Returns Zero on Bank Statement Columns
When you copy a transaction table from an online bank export or convert a statement PDF into Excel, you may find that typing =SUM(D2:D50) returns exactly 0.00, even when the column visually contains numbers like 1,250.00 and 45.20.
This happens because Microsoft Excel intentionally ignores text values inside calculation functions like SUM, AVERAGE, and STDEV. If Excel perceives a cell as text, it treats its mathematical value as zero.
CLEAN alone does not fix every whitespace issue. It removes the non-printing characters with codes 0 to 31, but not the non-breaking space, CHAR(160), and not ordinary spaces. Text copied from web pages and some PDFs can contain non-breaking spaces, and CLEAN leaves those in place.
The 4 Hidden Culprits in Bank Statement Text Data
1. Non-breaking spaces (CHAR 160): text copied from web pages (where they appear as ) and some PDFs can contain non-breaking spaces before or inside amounts. TRIM does not remove them, so the value stays text.
2. Trailing negative signs: some statements print debits with a trailing minus sign, such as "250.00-", which a formula or paste will treat as text.
3. Hardcoded Currency Symbols: If a currency symbol like "$" or "£" was typed or pasted into the cell rather than applied via cell formatting, Excel treats the whole entry as text.
4. Accounting Parentheses: Values formatted as "(125.50)" that are surrounded by text spaces fail standard numeric parsing.
Method 1: The "Text to Columns" Quick Fix (No Formulas)
For a column of plain numbers stored as text, Text to Columns is the fastest built-in converter. (In the wizard's final step, the Advanced button also has a "Trailing minus for negative numbers" option for values like 250.00-.)
1. Select the entire column of problematic amounts.
2. Go to the Data tab on the top ribbon and click "Text to Columns".
3. In the pop-up wizard, click "Finish" immediately without changing any options.
Excel re-evaluates the selected cells. If there are no trailing characters or currency symbols, the numbers will snap to the right-hand edge of the cells and your =SUM() formula will calculate properly.
Method 2: The "Paste Special > Multiply" Trick
You can force Excel to perform mathematical coercion on text numbers using Paste Special:
1. In any empty cell, type the number 1 and press Enter.
2. Copy that cell (Ctrl+C on Windows, Cmd+C on Mac).
3. Highlight your problematic bank statement amounts.
4. Right-click the highlighted cells, choose "Paste Special...", select "Multiply" under Operation, and click OK.
5. Delete the stray "1" you typed in step 1.
Multiplying text by 1 forces Excel to evaluate the characters as numbers.
Method 3: The Comprehensive Formula for CHAR(160) and Symbols
When statement cells contain non-breaking spaces, currency symbols, and text padding, use this formula in an adjacent column to sanitize the string into a true numeric value:
=VALUE(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), CHAR(160), ""))))How this formula works:
• SUBSTITUTE(A2, "$", "") strips the currency symbol.
• SUBSTITUTE(..., CHAR(160), "") removes non-breaking spaces, including ones used as thousands separators (such as "1 250.00"), which TRIM would otherwise leave inside the number.
• CLEAN(...) removes non-printable ASCII characters 0–31.
• TRIM(...) removes leading and trailing standard spaces.
• VALUE(...) converts the cleaned text into a number. VALUE follows your regional settings, so "1,250.00" converts where the comma is the thousands separator but not where it is the decimal separator.
Method 4: Handling Trailing Negative Signs (e.g., 250.00-)
If your statement uses trailing minus signs for debits or withdrawals, use an IF formula that checks the right-most character:
=IF(RIGHT(TRIM(A2), 1)="-", -VALUE(SUBSTITUTE(TRIM(A2), "-", "")), VALUE(TRIM(A2)))If the last character is a minus sign, the formula strips the minus, converts the string to a number, and negates it. Otherwise, it converts the value as a positive number.
Automated Conversion Alternative
In the Excel (.xlsx) file from PDFtoRow, amount and balance columns are stored as real numbers, so SUM works immediately. A number format keeps them looking as printed (for example a currency sign, parentheses, or a trailing DR/CR), while the cell value itself is numeric. The CSV file contains plain numbers. Always compare the totals with your statement before relying on the file.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
By default, Excel aligns numbers to the right side of a cell and text to the left side. You can also test a cell with =ISNUMBER(A2). If it returns FALSE, the cell is stored as text.
Excel's TRIM function only removes standard ASCII space 32. It does not recognize non-breaking space CHAR(160). You must use SUBSTITUTE(A2, CHAR(160), " ") before applying TRIM.
Yes. Highlight the range of cells showing green error triangles, click the yellow exclamation point icon that appears beside the top cell, and select "Convert to Number".
Primary Evidence & Official References
- Convert numbers stored as text to numbers in Excel — Microsoft Support (Verified 2026-10-15). Official source documentation →