Excel won't sum bank statement amounts: fix text numbers without losing the originals

Check the cell, not its appearance

You'll need the original amounts beside the converted ones if a negative changes sign or a separator changes an amount's size. Save an untouched copy of the bank export, then work in a separate Excel workbook with a column named Raw amount and a source row or reference beside it. Leave that column alone: don't overwrite it with formulas or paste converted values back over it.

With a raw amount in B2, enter =ISNUMBER(B2) in an empty cell. TRUE means Excel already holds a number; FALSE means it doesn't. =ISTEXT(B2) checks specifically for text. Microsoft's IS function reference, updated July 24, 2026, says these tests don't convert their arguments, so text containing digits still returns FALSE for ISNUMBER. A blank also fails ISNUMBER, which is why FALSE alone doesn't tell you what's wrong.

If the amount is already numeric, there's nothing to convert. Leave it alone and inspect the SUM range and the underlying amount against the statement. If it's text and the punctuation matches your Excel settings, copy it to a working column and try the warning menu's Convert to Number. Microsoft documents that option and a helper-column alternative in its conversion instructions, updated September 11, 2026. Don't select the entire worksheet: reference 001234 is an identifier, not 1,234 units of money.

Tell Excel which punctuation belongs to the number

For ordinary text numbers that Excel already understands, a separate cell containing =IF(B2="","",VALUE(B2)) is a short conversion route. The VALUE reference, updated June 25, 2026, says the function accepts number, date and time formats Excel recognizes. That makes it useful for conversion, but not for validating bank amounts: success doesn't prove that the source was money.

A bank export with a different regional convention needs NUMBERVALUE with explicit separators instead. Its second argument is the source decimal separator and its third is the grouping separator. These two formulas express the same amount:

=NUMBERVALUE("1,234.56",".",",")
=NUMBERVALUE("1.234,56",",",".")

Both results are 1234.56. Microsoft's NUMBERVALUE page, updated June 25, 2026, documents this locale-independent conversion. Omitting the separators uses the current locale instead. A lone 1,234 is ambiguous: it could mean 1234 or 1.234. Check the statement's convention rather than choosing the interpretation that makes a total look plausible.

The formulas here use English function names and commas between arguments. If your Excel expects semicolons, change those argument separators, not the punctuation inside quoted strings. The decimal-comma example then becomes =NUMBERVALUE("1.234,56";",";"."). Microsoft explains this distinction in its formula separator guidance, updated April 15, 2026. You don't need to change global regional settings just to repair one bank column.

Each source file needs one documented convention. If files genuinely use different conventions, convert them separately before combining them. For the date side of the same problem, see bank CSV dates and number formats.

A fictional sheet with seven amounts and one missing value

This fictional example mixes several text representations so you can try the repairs. Assume every amount belongs to one USD bank account, decimal points mark cents, and negative values mean money leaving. The last record has an unknown amount, not a zero, so it must remain unresolved.

To try it, save the sample as a CSV and import it into A1:B9, setting both columns to Text before loading. Quotation marks around a CSV field don't instruct Excel to keep it as text; the import steps below show how to do that. The space in the penultimate amount is a real non-breaking space, U+00A0, between 1 and 000.

Reference,Raw amount
001234,"1,250.50"
001235,-25.25
001236,4.75
001237,10.00-
001238,(5.00)
001239,$ 20.00
001240,1 000.00
001241,

A and B stay untouched while the helper columns do the work. Name C1 Cleaned text, D1 Signed text, E1 Numeric amount and F1 Conversion status. Enter each formula below in its named cell, without the cell-label prefix, then fill C2:F2 down through row 9. The helper cells need General format so the formulas calculate.

C2: =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,UNICHAR(160)," "),UNICHAR(8239)," "),"$",""))
D2: =IF(C2="","",IF(RIGHT(C2,1)="-","-"&LEFT(C2,LEN(C2)-1),IF(AND(LEFT(C2,1)="(",RIGHT(C2,1)=")"),"-"&MID(C2,2,LEN(C2)-2),C2)))
E2: =IF(D2="","",NUMBERVALUE(D2,".",","))
F2: =IF(C2="","Missing",IF(ISNUMBER(E2),"Numeric","Review"))

The first helper uses SUBSTITUTE to replace two known space characters with ordinary spaces and remove the confirmed USD symbol, then TRIM removes surrounding spaces. Microsoft's SUBSTITUTE reference (updated July 24, 2026) confirms that omitting its optional instance argument replaces every occurrence. The second helper moves a trailing minus to the front, or replaces enclosing parentheses with a leading minus. The third converts that text using the agreed punctuation. The fourth labels the conversion result, not the transaction's correctness.

These helpers assume you've inspected the source patterns. They aren't for arbitrary mixed text, account numbers, multiple currencies, percentages or ambiguous CR/DR labels. A cell containing only a currency symbol becomes Missing, not a successful zero. If you find unexpected letters or malformed signs, investigate them against the source rather than removing characters until the cell happens to calculate.

Keep the sign while removing the obstruction

Deleting parentheses from (5.00) would turn a negative into a positive. Likewise, deleting the last character from 10.00- without adding a leading minus loses the sign. The helper preserves those meanings only after you confirm that the bank uses those notations for negatives. It doesn't decide whether a credit-card purchase should be positive or negative in a particular destination file.

Currency symbols need the same care. Removing $ from a confirmed USD amount is text cleanup, not currency conversion. For a GBP-only or EUR-only file, replace the literal symbol in C2 with the one actually present. If the column mixes dollar, pound and euro symbols, don't strip them and add the results together. You need a currency column and separate totals.

Spaces are less visible. Microsoft's TRIM reference, updated June 25, 2026, says TRIM alone does not remove non-breaking space 160. The helper uses UNICHAR to target that character and narrow non-breaking space 8239 explicitly. NUMBERVALUE then ignores ordinary spaces, including inside numbers. That explains why the sample converts, but also why you need to check a strange space between digits: the function can't know whether it was legitimate thousands grouping or a damaged extraction.

For a decimal-comma file, keep the sign-cleaning steps but change E2 to =IF(D2="","",NUMBERVALUE(D2,",",".")). Don't globally delete commas: in 1.234,56 the comma separates the fractional amount. The original column is your evidence for choosing the right rule.

Separate Debit and Credit columns pose a different layout problem. Their numeric text still needs converting without changing what each column means. After that, the debit and credit converter can give you a signed amount column if you need one. Check its selected convention against the source account, because a negative number doesn't always mean spending.

A working SUM can still be incomplete

The sample's first three numeric amounts sum to 1,230.00. All seven numeric amounts sum to 2,235.00, but the eighth source record is missing an amount. That makes 2,235.00 a subtotal of converted amounts, not the complete account movement. The unresolved row needs to stay in the worksheet, and the affected import must wait until its amount is resolved.

=SUM(E2:E4)
=SUM(E2:E9)
=COUNT(E2:E9)
=COUNTIF(F2:F9,"Missing")
=COUNTIF(F2:F9,"Review")

The expected results, in order, are 1230.00, 2235.00, 7, 1 and 0. That accounts for all eight source records: seven numeric conversions and one missing value. The empty result in E9 is an empty string returned by a formula, not a truly empty cell. The explicit blank guard matters because Microsoft documents that NUMBERVALUE converts an empty string to zero. A genuine source 0.00, on the other hand, should convert to numeric zero and be counted.

Microsoft's COUNT reference (updated July 24, 2026) confirms that a referenced range counts only numbers, not text or errors. The COUNTIF reference (updated August 7, 2026) documents the text matching used for Missing and Review. If E contains a conversion error, SUM returns an error rather than skipping that row. Use F to find the failure and repair it against the source; Microsoft's formula-error guidance (updated June 25, 2026) explains how errors carry into formulas that reference them.

Wrapping conversion errors in IFERROR(...,0) hides the problem. As Microsoft's IFERROR reference (updated July 24, 2026) explains, the second argument replaces an error, so that formula disguises a failed amount as a real zero. Even zero Review rows aren't enough: NUMBERVALUE ignores grouping separators before the decimal without checking three-digit groups. With decimal point and comma grouping, 1,23.45 becomes 123.45, but 1.23,45 returns #VALUE! because the grouping separator follows the decimal. It also accepts trailing percentage signs: 9%% becomes 0.0009 under its documented rules. Keep such values out of the conversion workflow until you understand them, but don't silently omit their transactions from a supposedly complete result.

Once every transaction has a reviewed amount, compare the row count, positive and negative subtotals, and individual changed rows with the source. If the statement provides opening and closing balances, check opening balance plus signed movement against closing balance for that same account and period. A matching total is only an arithmetic check. Equal and opposite row errors can cancel, and the check doesn't establish document authenticity or reconcile the entries to your books.

For repeat imports, set the types before loading

If the same bank export needs repair every month, define its import rather than repeating manual edits. Microsoft's Power Query data-type guidance, updated September 8, 2026, explains that type detection is automatic for unstructured sources such as CSV. A guessed type doesn't tell you whether every row uses that convention.

1. Open Data > From Text/CSV, then the query editor from the preview, where you can inspect the delimiter and raw values before loading.
2. The raw amount and identifier columns need to stay as Text. If prompted, choose Replace Current, rather than adding Text after an earlier numeric conversion. Inspect Applied Steps for a prior inferred conversion that has already changed the source, then remove or replace it and compare the result with the untouched CSV.
3. Keep that raw text column and make a working copy for the conversion. On the copy, resolve confirmed currency symbols, spaces and nonstandard negative signs before right-clicking its header and choosing Change Type > Using Locale. Select an appropriate numeric type and the source locale, such as German (Germany) for a confirmed German-format amount column. Errors and blanks need review, not row removal.

Microsoft's identifier-preservation instructions (updated June 25, 2026) document the Text and Replace Current choices. Its locale guidance (updated September 17, 2026) documents Using Locale and explains that this conversion setting overrides the query's locale, which in turn overrides the operating system locale. If a previously saved workbook has already lost digits, you'll need the original export. Changing the column to Text afterward can't recreate them.

If you prefer the older import wizard, Microsoft's Text Import Wizard documentation, updated August 12, 2026, identifies it as a legacy feature. Its third step's Advanced options let you specify decimal and thousands separators and trailing-minus negatives. Keep identifiers as Text and import into a separate worksheet. These are documented legacy-wizard options; this doesn't mean Power Query has the same trailing-minus checkbox.

If the file opens as one column or characters are garbled, fix that earlier problem with the CSV format repair tool. It prepares a new encoding/separator copy, but it doesn't normalize amount values or inspect an Excel workbook's cell types. The source-aware conversion and counts above are still needed.

What we checked

Microsoft sources linked here were opened on October 18, 2026. The update dates above are their observed page metadata dates, not dates when we tested an Excel release. We parsed the fictional CSV and checked the helper-formula logic and totals with a Python Decimal evaluator, including 500 generated cases covering signs, Unicode spaces and separator choices, plus blank, zero and error cases. This wasn't an Excel calculation engine, and we didn't run the formulas or import menus in desktop Excel or Excel for the web. Try the fictional sheet in your version first, comparing each converted amount before applying the method to a bank file.