Excel formulas to clean up a bank statement after import

If your converter dropped a bank statement into Excel but the rows still need normalization before import or analysis, these are the formulas to know. None of them are exotic. All of them save afternoons.

The common cleanup tasks

Most messy bank-statement imports share the same flaws. Merchant names carry trailing whitespace or non-printing characters from the PDF. Dates show up in two formats in the same file because the bank changed its layout mid-year. Debit and credit live in one column with parentheses for negatives. Amounts come glued to a currency symbol ("$1,234.56") so Excel reads them as text. Multi-line descriptions arrive split across cells.

Fix in order: text first, then dates, then numbers, then a balance check at the end. Wrong order means redoing work.

Formulas worth memorizing

These are the seven that cover roughly 90% of bank-statement cleanup.

• =TRIM(A2). Removes leading, trailing, and double internal whitespace from descriptions. Bank PDFs love trailing spaces.
• =CLEAN(A2). Strips non-printable characters (carriage returns, null bytes) that come out of OCR. Wrap with TRIM: =TRIM(CLEAN(A2)).
• =TEXT(A2, "yyyy-mm-dd"). Coerces a date to ISO 8601. Useful when the bank mixes "Jan 5" and "01/05" in the same statement.
• =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")). Forces a string like "$1,234.56" into a real number Excel can sum.
• =IFERROR(IF(SEARCH("AMZN",A2)>0,"Amazon",""),""). Categorize by keyword. SEARCH returns an error when no match; IFERROR turns that into a blank.
• =SUMIF(C:C,"Amazon",B:B). Totals all transactions tagged Amazon in column C, with amounts in column B.
• =B3-B2. Quick delta on running balance to spot off-by-one transcription errors.

You can chain them. =VALUE(SUBSTITUTE(TRIM(B2),",","")) handles a string with whitespace, commas, and an embedded number in one shot.

A balance check formula

This is the one most people skip and shouldn't. After cleanup, validate that the math reconciles:

= opening_balance + SUMIF(D:D,">0") + SUMIF(D:D,"<0")

If column D holds signed amounts (positive for credits, negative for debits), the sum should equal your closing balance to the cent. If it doesn't, you dropped a row, double-counted one, or miscoded a sign. Find the gap before importing, because reconciliation downstream is worse than reconciliation in the same Excel file.

Separate debit and credit columns? = opening_balance + SUM(credits) - SUM(debits).

When this isn't enough

• Multi-account statements (joint household checking, savings, credit card all in one PDF). Power Query or a pivot table is faster than per-row formulas. Split by account, then run the seven formulas above on each sheet.
• OCR'd statements from photos or scans. The text artifacts vary row by row, so a one-shot SUBSTITUTE doesn't catch everything. Sort by description length to find the weird rows and review them by hand.
• Foreign currency conversion. Don't try to do FX in a SUMIF. Pull the rate from a real source and use a helper column.

FAQ

Power Query vs formulas, which is better? Power Query is better when you're going to repeat the same cleanup every month. Formulas are better for a one-off review. If you'll import 12 statements this year, build the Power Query once. For a single tax review, formulas are faster to write than the M language is to learn.

How does Bank2XL avoid all this cleanup? The extraction stage already runs TRIM, sign normalization, date coercion, and a reconciliation check. The Excel output has one row per transaction, signed amounts, ISO dates, and a Validation tab showing whether opening + credits - debits = closing.

Get started

If you're cleaning up because your converter didn't do this for you, install Bank2XL from the Chrome Web Store at bank2xl.app. Free during beta: 10 statements a day, no signup. The Excel comes out already trimmed, signed, ISO-dated, and reconciliation-checked.

← Back to all guides