Bank statements

Axis Bank statement PDF to Excel: convert and tidy it

Convert an Axis Bank PDF or CSV statement into Excel and split the UPI narration into useful columns with TEXTSPLIT, then verify the closing balance.

Editorial illustration for Axis Bank statement PDF to Excel: convert and tidy it

To convert an Axis Bank statement to Excel, use the CSV or Excel download if your own login offers one. If only a PDF exists, use Data, Get Data, From File, From PDF in Excel for Microsoft 365 or a recent desktop Excel, load the transaction table, and then split the long narration text into separate parts so that you can see who was paid.

What does a converted narration look like?

Narration is where the useful information sits, and it usually arrives as one long text with separators. The bank's exact format is not something we can promise, so the example below is invented to show the idea:

  • UPI/FRESHMART GROCERS/payment/4821
  • NEFT/HOUSING SOCIETY/maintenance
  • POS/QUICKBITE RESTAURANT/Mumbai

If your narration looks something like this, with a slash between parts, you can pull out the names in two ways.

How do I split the narration?

The easy, older way is Data, Text to Columns, Delimited, with the slash as the separator. It overwrites the cells to its right, so first insert a few empty columns after the narration. The formula way needs Excel 365 or Excel 2021, because TEXTSPLIT is not available in older versions:

  1. =TEXTSPLIT(D2,"/") spreads the parts across cells to the right of D2.
  2. =INDEX(TEXTSPLIT(D2,"/"),1,2) returns just the second part, which, in the invented example, is the name.
  3. =TRIM(INDEX(TEXTSPLIT(D2,"/"),1,2)) removes stray spaces around the name.

TEXTSPLIT returns an error if the separator you named does not appear. Wrap it, like =IFERROR(TRIM(INDEX(TEXTSPLIT(D2,"/"),1,2)),D2), to fall back to the original narration for odd lines.

How do I clean the numbers and dates?

Amounts that arrive as text, such as 1,250.00, will not add up. Convert them with =VALUE(SUBSTITUTE(E2,",","")). Dates stored as text can be converted with =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) when they are in day/month/year form. Copy the finished columns and paste them as values, then delete the originals.

What about Google Sheets?

Sheets has no TEXTSPLIT of the same kind, but it has SPLIT. =SPLIT(D2,"/") spreads the parts of the narration to the right, just as Text to Columns does, and =INDEX(SPLIT(D2,"/"),1,2) returns the second part. Because SPLIT treats each character in the delimiter as a separator, use a single slash rather than a longer string. Sheets cannot read a PDF table directly, so import a CSV or Excel version, or convert the PDF in Excel first and then upload the result to Drive.

How do I list the top names for the month?

Once the name is in its own column, say column H, and the debit in F, a pivot table gives you the spend by name. In a formula, the total for one name is:

  • =SUMIFS($F$2:$F$500,$H$2:$H$500,"FRESHMART GROCERS")
  • Replace the name with a cell reference such as J2 to fill the formula down a list.

How do I verify the result?

Take a month with an opening balance of ₹14,200, credits of ₹46,800 and debits of ₹39,950. The closing balance should be ₹14,200 plus ₹46,800 minus ₹39,950, which is ₹21,050. In Excel, with the opening balance in B1, enter =B1+SUM(G2:G500)-SUM(F2:F500) and compare the result with the printed closing balance. If it is off, check three usual suspects in turn: a page-end line that was read as a transaction, a debit that sits in the credit column, and a row where the narration wrapped onto a second line and created a half-empty row. Each one is quick to spot once the data is sorted by date and the narration column is open in front of you.

Work on a copy of the file and keep the original narration column untouched, even after you have split it. If a split goes wrong for one odd line, the original text is still there to read. Hide the helper columns once they are checked, rather than deleting them, so that the logic is visible later.

When should I stop using formulas for this?

If you find yourself splitting narration for twelve months in a row, it is worth checking whether uploading the statement to SpendixAI would save time, since it normalises merchant names and lets you add your own rules. A sheet is still handy for a one-off month or a tax-time check.

SPENDIXAI JOURNAL

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

Explore your reports