Spreadsheets

Why a bank statement CSV opens in a single column in Excel

Why a bank statement CSV opens in one column in Excel, how to tell which of three causes you have, and how to split it with Text to Columns.

Editorial illustration for Why a bank statement CSV opens in a single column in Excel

A statement CSV opens in a single column when Excel is looking for a different separator from the one in the file, or when each whole line is wrapped in quotation marks. Open the file in Notepad to see which, then fix it with Text to Columns or by importing through Data > From Text/CSV.

How do you tell which cause you have?

Right-click the CSV, choose Open with, and pick Notepad. Look at one data line, then match it below.

What Notepad showsCauseFix
Fields separated by commas, but Excel shows one columnYour Windows list separator is not a commaChange the separator, or import with a chosen delimiter
Fields separated by semicolons, tabs or pipesThe file uses a different delimiterText to Columns with that delimiter
Each whole line starts and ends with a quotation markExcel sees one quoted field per lineText to Columns with comma and quote qualifier, or remove the outer quotes

What is the list separator setting?

When you double-click a CSV, Excel splits it using the list separator from Windows regional settings, not from the file. In some regional setups that character is a semicolon, so a comma file stays in column A. To check it, open Control Panel, choose Region, click Additional settings and read the List separator box. Changing it affects other programs too, so many people prefer the import route described below.

How does Text to Columns fix it?

  1. Select column A, from the first row to the last.
  2. Choose Data > Text to Columns and pick Delimited, then Next.
  3. Tick the delimiter you saw in Notepad. For a pipe or semicolon use Other and type the character. Leave Text qualifier as the double quotation mark.
  4. On the last step, click the date column and the reference columns and set them to Text so nothing is reformatted. Choose a Destination to the right if you want to keep the original column.
  5. Click Finish, then widen the columns and check the first and last rows.

Text to Columns works on one column at a time and changes cells in place, so work on a copy of the file.

What if every line sits inside quotation marks?

Some exports wrap the full row in one pair of quotes. Excel then treats the row as a single value that happens to contain commas. Try Text to Columns first with comma as the delimiter. If the first and last columns keep a stray quotation mark, use Home > Find & Select > Replace, type a double quotation mark in Find what, leave Replace with empty, and apply it to those two columns only. Scan the narration afterwards in case a quotation mark belonged to the text.

The more reliable route

Instead of double-clicking, open a blank workbook and choose Data > Get Data > From File > From Text/CSV. The preview lets you choose the delimiter yourself, so your regional setting stops mattering. You can then load the data or transform it. That also protects long reference numbers and dates, which Text to Columns can still alter.

If you handle statements from more than one bank, expect different separators. Write the delimiter for each account in your own notes, so you recognise the pattern at a glance instead of trying every option. A minute of noting saves a lot of trial and error later.

A final sanity check

  • Does the number of columns match the header in Notepad?
  • Do the debit, credit and balance columns hold numbers that align to the right? Text-looking numbers stay on the left and will not add up.
  • Does the row count equal the number of transactions on the statement?

If those hold, you can save the result as an XLSX and keep the original CSV untouched.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports