Bank statements

Santander statement to CSV: merge money in and out into one column

Convert a Santander statement to CSV and tidy it: merge the in and out columns, fix the dates, and check your totals against the closing balance.

Editorial illustration for Santander statement to CSV: merge money in and out into one column

To turn a Santander statement into CSV, look in your online account for a download or export option and choose a spreadsheet-friendly format if one is offered; if you only get a PDF, convert carefully or use a tool that reads PDFs. The real work comes after: most bank files keep money in and money out in separate columns, and you will want one signed amount.

What does a bank CSV usually contain?

Layouts vary by bank and by account type, so open your own file and read the header row. You will typically find a date, a description, one or two amount columns and a running balance. Do not assume the order; map each column by its header name.

Column in the fileWhat to do with it
DateCheck it is a real date, not text
DescriptionKeep as is; it feeds categorising later
Money in and money outMerge into one signed amount
BalanceKeep it only to check your totals

How do I merge the in and out columns?

Say column C is money in and column D is money out, with blanks where nothing applies. In E2 type =N(C2)-N(D2) and fill down. N turns a blank or text into zero, so the sum works on every row. Excel and Google Sheets both support it. Spending then shows as negative and income as positive.

A hypothetical week of rows: salary 45,000.00 in, rent 18,000.00 out, groceries 2,350.50 out, a refund 499.00 in. The signed amounts are +45,000.00, -18,000.00, -2,350.50 and +499.00. The sum is 25,148.50 (45,000 - 18,000 - 2,350.50 + 499). If the balance column moved by the same amount across those rows, your merge is right.

What if the dates look wrong?

A date such as 03/10/2026 is read differently depending on your region settings, so the sheet may treat it as 3 October or 10 March. Check one date you know. If the sheet misreads the file, import it using Data, then From Text/CSV and set the date column format explicitly, or split the text with =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) if the file is day-first.

Can I upload it to an expense tracker instead?

Yes. SpendixAI takes PDF, CSV or Excel statements; you pick the account, upload, and review the rows before they are saved. Merging columns by hand is only needed if you want to analyse the file in a sheet. Check the base currency before importing, because it cannot be changed once transactions exist. SpendixAI is built around India and INR, so think about whether it fits an account in another currency.

Cleaning tips worth the minute

  • Remove blank rows and any summary lines at the bottom.
  • Keep one header row only.
  • Save as CSV with UTF-8 if your sheet offers the choice, to keep symbols intact.
  • Name the file with the bank, account and month.

How do I tag the rows so they are useful?

Once amounts are merged, add a category column and fill it by matching words in the description: for example, any row containing the word rent gets the label Rent. A helper formula such as =IF(ISNUMBER(SEARCH("rent",B2)),"Rent","") marks those rows, and you can nest more tests or use IFS in Excel 2019 or later and Google Sheets. Check the unmarked rows by eye; they are usually the interesting ones.

Final check

  1. Row count matches the statement.
  2. Merged total matches the change in balance.
  3. Dates read correctly in a spot check.
  4. Original file kept untouched.
SPENDIXAI JOURNAL

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

Explore your reports