An MT940 file is a SWIFT-format bank statement in plain text, where each line starts with a tag such as :61: for a transaction. Getting MT940 to Excel means reading the file line by line, turning each :61: line and its :86: details into one row, and keeping the opening and closing balances to check the result.
What an MT940 bank statement file contains
An MT940 bank statement file is a text file of tagged fields describing one account's statement: a reference, the account, a statement number, an opening balance, the transactions and a closing balance. Banks publish their own descriptions of the layout; ING's MT940 and MT942 format description is a clear example.
According to that ING document, the main tags are:
- :20: transaction reference number, which identifies the message.
- :25: account identification.
- :28C: statement number, split into a statement number and a page number.
- :60F: opening balance: a debit or credit mark, a date in YYMMDD form, an ISO 4217 currency code and the amount.
- :61: statement line, one per transaction, repeating as often as needed.
- :86: information to the account owner, up to 6 lines of 65 characters, following the :61: it describes.
- :62F: closing balance, in the same form as :60F:.
The same document says amounts use a comma as the decimal separator, so 2450,00 means 2,450.00. Banks vary in how they fill optional subfields, so read your own bank's description before relying on any one layout.
Here is a fictional example file for a fictional account ending 4821, with the Excel columns each line becomes:
:20:STMT20260331 -> statement reference
:25:EXAMPLEBANK/0000004821 -> Account
:28C:00031/00001 -> statement 31, page 1
:60F:C260330USD18402,17 -> Opening balance 18,402.17 (credit) on 2026-03-30
:61:2603310331C2450,00NTRFNONREF//RE260331-0001
-> Date 2026-03-31 | Sign C | Amount 2,450.00 | Type NTRF
:86:ACH DEPOSIT NORTHGATE MEDIA
INVOICE 2026-118 MARCH RETAINER
-> Description (two lines joined)
:61:2603310331D412,50NTRFNONREF//PA260331-0002
-> Date 2026-03-31 | Sign D | Amount -412.50 | Type NTRF
:86:PAYMENT TO EXAMPLE OFFICE SUPPLY
-> Description
:62F:C260331USD20439,67 -> Closing balance 20,439.67 (credit) on 2026-03-31
The check is simple: 18,402.17 plus 2,450.00 minus 412.50 equals 20,439.67. If the rows you build do not reach the closing balance, a line was missed.
Can Excel open an MT940 file directly?
Excel can open an MT940 file as text, but not as a table. The file has no single delimiter between fields, so every line lands in one column: tags, dates, amounts and narrative all squeezed into column A. The data is there; it just has not been split into columns yet.
Microsoft's article on importing or exporting text files in Excel says a .txt file starts the Import Text Wizard, while a .csv file opens directly using the default data format settings. Saving or renaming the MT940 file as .txt and opening it gives you the wizard and a preview, but an MT940 line still has nothing consistent to split on.
That is why the conversion happens in two steps. First, get each line into its own row in column A. Second, read the tags and build a proper table beside it, as the next section describes. Before doing either, decide which columns the result needs; the post on bank statement CSV format sets out a column layout that works for most uses.
Converting MT940 lines to Excel rows
Converting MT940 lines to Excel rows means turning each :61: statement line into one row with a date, a sign and an amount, then attaching the description from the :86: line that follows. The :61: line packs its subfields together without separators, so you split it by position and by the letters that mark the debit or credit.
The ING description gives the :61: subfields in order:
- Value date, six digits, YYMMDD. In the example, 260331 is March 31, 2026.
- Entry date, four digits, MMDD, optional. Here, 0331.
- Debit or credit mark: D for debit, C for credit, RD for a reversal of a debit, RC for a reversal of a credit.
- Funds code, one optional letter.
- Amount, with a decimal comma.
- Transaction type code, such as NTRF for a fund transfer.
- Reference for the account owner, then // and the bank's own reference.
In Excel, Microsoft's MID function returns a set number of characters from a text string, starting at a position you choose. With the line in A5 and the tag removed, =MID(A5,1,6) returns the value date. The mark and amount need more care, because the mark can be one or two letters and the amount length varies. Find where the amount ends by locating the transaction type code, then take what lies between.
Convert the amount in three moves: replace the comma with a period, turn the text into a number, and make it negative when the mark is D or RC. Keep the mark in its own column as well. A reversal row is easy to misread later if only the signed amount survives.
MT940 to Excel edge cases: multi-line :86: details
The :86: field can run over several lines, and the lines after the first carry no tag. When you get MT940 to Excel line by line, those continuation lines look like orphans in column A. They belong to the :86: above them and must be joined back before the description is copied into the transaction's row.
The rule that works: any line that does not start with a colon and a tag is a continuation of the previous tagged line. Join it with a space. In the example above, "INVOICE 2026-118 MARCH RETAINER" joins "ACH DEPOSIT NORTHGATE MEDIA" to form one description.
Some banks put structure inside :86:, using codes or separators to mark the counterparty name, account and remittance text. The ING document notes that formats can be structured or unstructured. If your bank uses a structured :86:, its format description will list the codes, and you can split them into their own columns.
Two more cases come up. A file may hold several statements, one after another, each with its own :20: and balances; start a new block at each :20:. And a :86: can follow :62F: as information about the whole statement, not a transaction, so do not attach it to the last row.
Is an MT940 parser online safe for statement files?
An MT940 parser online is a website that converts the file for you. Whether it is safe depends on who runs it and what happens to the file. An MT940 file carries the account identification, the opening and closing balances, every transaction amount, and counterparty names and references in :86:. Uploading it shares all of that.
Before uploading a client's file, check the basics. Who operates the site, and is there a named company behind it? Does it say where files are processed and how long they are kept? Does the firm's own policy allow client bank data to go to that service? If the answers are unclear, do the conversion locally in Excel with the steps above.
A firm that already handles statements as PDFs may not need a separate converter. Schema CPA reads bank statements and exports the rows to Excel or CSV, alongside the other document types listed on the homepage; Get started to try it on a client's file. MT940 data is close to what a bank feed delivers, and the post on bank feed versus bank statement reconciliation explains how the two differ. For banks that offer spreadsheet downloads instead, see exporting bank statements to Excel and the Chase CSV download guide.