Amounts import as text and SUM returns zero

You have the converted file open, you have selected the amount column, and the status bar shows a count but no sum. SUM over the range returns zero. The values are plainly there, formatted the way they were on the statement, and every one of them is left aligned in its cell.

Left alignment is the first half of the diagnosis. Excel and Google Sheets right align numbers and left align text by default, so a left aligned amount column is usually a column of strings that happen to look like money. Usually, not always: alignment can be set explicitly by a person or carried in by a style, so confirm the reading rather than trusting the look. ISNUMBER on one cell settles that cell, and COUNT over the range tells you how many cells hold a real number. SUM skips text silently rather than erroring, which is why the result is zero rather than a complaint. There are four things that put a statement amount into that state, and each has a specific fix.

The second half of the diagnosis is the part worth slowing down for. An amount column that arrives as text is rarely an isolated formatting defect. It is a signal that whatever produced the file read the page as positioned characters and passed them through without ever deciding what a transaction was. A tool that did not resolve a DR suffix into a sign did not resolve the sign anywhere, and it had no way to check its own work. The rows you can see are the ones where the damage is visible. The rows that parsed cleanly went into the file unexamined.

Confirm it is actually text first

Before fixing anything, check how much of the column is affected. Put COUNT over the amount range and compare it against COUNTA. COUNT tallies numeric cells only; COUNTA tallies everything non-empty. If COUNT returns zero, the whole column is text. If it returns something in between, you have a mixed column, which is more dangerous than a fully broken one because partial totals look believable.

Mixed columns usually mean one of the four causes below applies only to some rows: the negatives are bracketed and the positives are not, or the four-figure amounts carry a separator and the three-figure ones do not. Whatever total you have been reading off that column is currently the sum of the rows that happened to parse.

Cause one: thousands separators

A separator is only readable if it matches what your spreadsheet expects. In an English locale, 1,240.00 parses as a number and 1.240,00 does not. In a German or Spanish locale, the reverse is true. Statements from continental European banks routinely use a full stop for thousands and a comma for decimals, and importing one into an English-locale sheet turns every four-figure amount into text while the three-figure amounts convert normally. That is the classic mixed column.

There is a second variant that catches people on English statements too. If the extraction inserted a space around the separator, or used a narrow no-break space as the separator, which is the standard in French and some Nordic layouts, the cell contains a character your spreadsheet does not recognise at all and will not parse.

The fix in Excel. Do not strip the separator by hand. Select the column, run Data then Text to Columns, click through to step three, and open Advanced. That dialog lets you declare which character the source uses for the decimal point and which for thousands, so Excel reads the incoming string on the source's terms rather than on your locale's. Set them to match the statement and the whole column converts in one pass. For a space separator, use find and replace to remove spaces from the column first, then convert.

The fix in Google Sheets. Sheets has no equivalent dialog, so you have two routes. Either set the spreadsheet's locale to the statement's country under File then Settings before you paste anything in, or normalise the string yourself with two find and replace passes: remove the thousands character first, then swap the decimal character for the one your locale expects. Do them in that order. Reversed, you end up with two characters that look alike and no way to tell which one was the separator.

Cause two: currency symbols in the cell

A statement that prints amounts as £1,240.00 or $1,240.00 sometimes carries the symbol into the extracted value. Excel parses a leading currency symbol in its own locale, so $1,240.00 usually converts in a US-locale sheet, but a foreign symbol, a currency code such as GBP or EUR sitting before the number, or a symbol placed after the amount as in 1.240,00 EUR will not parse.

The trailing-code case is the most common in practice, because multi-currency statements often label every line to keep the currencies distinguishable. That labelling is doing real work and should not simply be discarded, which is why the fix is to move it rather than delete it.

The fix. Insert a column, copy the currency code out of each amount, then remove the symbol or code from the amount column with find and replace. Replacing "EUR" with nothing, then trimming the residual space, leaves a clean numeric string. Keep the currency in its own column rather than dropping it, especially if the account holds more than one currency, because a blended amount column across currencies is not summable in any meaningful way afterwards.

Cause three: parenthesised negatives

Accounting convention writes a negative in brackets: (1,240.00) means minus 1,240.00. Excel does recognise this, which makes the failures here confusing when they happen. The parse breaks on small imperfections, and statements produce them constantly.

A space inside the brackets, as in ( 1,240.00 ), can stop the parse, and a narrow or non-breaking space in that position reliably does. A currency symbol between the bracket and the digits, as in (£1,240.00), stops the parse in a non-matching locale. A closing bracket that OCR read as a lowercase j or a 1 stops the parse and also corrupts the value. And a trailing minus sign, as in 1,240.00-, which is a convention on some mainframe-generated statements, is not recognised at all.

The fix. Normalise before converting. Find and replace "(" with "-" and ")" with nothing, then trim. That removes any dependence on the spreadsheet's bracket handling. It only leaves you with a number if the separators inside the string already match your locale; if they do not, what you have is a text string that now begins with a hyphen, so settle the separators using cause one above and handle the brackets afterwards. For a trailing minus, a formula that tests for a hyphen at the end and multiplies by minus one is more reliable than trying to make the format parse. Whether you want a single signed column at all depends on where the file is going, which is the subject of choosing between Excel, CSV, QBO and OFX as an export format.

Cause four: trailing DR and CR suffixes

UK, Irish, Indian, Australian and many Commonwealth statements do not use signs. They print the amount and mark direction with DR for debit or CR for credit: 1,240.00 DR. Neither Excel nor Google Sheets treats that as a number in any locale, so the entire column arrives as text, and unlike the other three causes this one affects every single row rather than a subset.

The suffix carries information you cannot afford to discard, because once it is gone the column has magnitudes with no directions. Stripping DR and CR and converting to numbers gives you a column that sums to a meaningless figure: every debit counted as a credit.

The fix. Do it in two moves. First, extract the suffix into its own column with a formula that reads the last two characters. Second, remove the suffix from the amount and convert the remainder to a number. Then build the signed amount in a third column: the numeric value where the flag is CR, and the negative of it where the flag is DR. Verify by checking that the credit subtotal and debit subtotal each match the statement's own printed totals before you collapse anything. Writing that conditional once per bank convention is exactly the work described in converting statements in Excel natively with Power Query.

Fixing a column end to end

For a column with more than one of these problems, the order matters. Capture signs, then remove noise, then convert.

1. 1

   Count how many cells are actually numeric

   Run COUNT and COUNTA over the range. A mixed column means the totals you have been reading are partial.
2. 2

   Capture the sign into its own column

   Extract any DR or CR suffix, bracket or trailing minus into a separate column before you touch the amounts.
3. 3

   Strip symbols and suffixes

   Use find and replace across the column for currency symbols, currency codes, brackets and suffixes, then TRIM to remove residual spaces.
4. 4

   Convert with the correct separators

   Run Text to Columns, and in step three open Advanced to declare the source's decimal and thousands characters explicitly rather than relying on your locale.
5. 5

   Apply the sign and reconcile

   Build the signed amount from the value and the captured direction, then check the debit and credit subtotals against the figures printed on the statement.

Run that sequence and the column will sum. Be precise about what you have achieved, because this is where most people stop and should not. Find and replace changed which characters are in the cell. Text to Columns changed how the cell is stored. Neither operation looked at the statement. If the extraction dropped a thousands separator from one four-figure amount, that amount is now a clean, right aligned, confidently wrong number sitting in a column that totals without complaint. The cleanup fixed the symptom you could see and left the ones you could not.

That last part is the one a spreadsheet cannot do for you at any price. A spreadsheet has no idea what the column is supposed to add up to. A conversion that reads the statement as a statement does: it carries the printed opening and closing balances through with the rows, and checks every row's running balance against the row before it, so an amount that came out with the wrong magnitude or the wrong direction breaks the chain instead of settling quietly into a column that totals without complaint.

Checking the result

The only check that matters is arithmetic, because a column that converts cleanly can still be wrong in every sign.

Take the debit and credit subtotals separately rather than only checking the net. A sign error leaves the net wrong but leaves the row count and the absolute total intact, so it hides from any check that works on magnitudes alone. Confirming that opening balance plus movements equals the printed closing balance is the strongest automatic check available on an amount column, and it catches the error class that matters most here, a wrong digit or a wrong sign, because either one breaks the arithmetic. It is not a proof that every row is right. It says nothing about dates or descriptions, and two errors that offset one another exactly, an amount overstated on one row and understated by the same figure on another, will pass it. Taking the debit and credit subtotals as well as the net closes most of that gap, because an offsetting pair then has to survive three totals rather than one.

Check the largest amount by hand as well. It carries the most separators and is therefore the value most likely to have converted to the wrong magnitude rather than failing outright, and an amount that silently lost a thousands separator is the one error in this whole set that a subtotal check will catch but an eyeball check will not. If dates in the same file are also arriving as strings, the date column has its own set of causes worth resolving in the same pass, and the two arriving together is strong evidence that the extraction never understood the table at all.

The requirement does not change with the route you take. The amount column has to arrive as numbers that carry their direction, and those numbers have to tie back to the figures the bank printed. A column that merely looks numeric has met neither condition, and no amount of formatting will tell you which of the two it failed.