To get an RBL Bank statement from PDF into a tidy Excel table, convert the file, make sure every amount is a real number, split money out from money in, and check the totals against the closing balance. The step worth getting right is the debit and credit split, because layouts differ and formulas depend on which one you have.
Which layout does your statement use?
We do not know the exact column order of an RBL Bank statement, and it may change by account type. Look at yours and find which of these three it follows, because each needs a different fix.
| If the file shows | Then | Formula for debit (amount in D, marker in E) |
|---|---|---|
| Separate debit and credit columns | No split needed, only convert text to numbers | Not needed |
| One amount column with a Dr or Cr marker | Split by the marker | =IF(E2="Dr", VALUE(SUBSTITUTE(D2, ",", "")), 0) |
| One signed amount column, with minus for debits | Split by the sign | =IF(D2<0, -D2, 0) |
How do you split a single amount column?
Say amounts are in D, the Dr or Cr marker is in E, and you want debit in F and credit in G.
- F2: =IF(E2="Dr", VALUE(SUBSTITUTE(D2, ",", "")), 0)
- G2: =IF(E2="Cr", VALUE(SUBSTITUTE(D2, ",", "")), 0)
- Fill both down to the last row.
- If the markers are written DR and CR in capitals, the comparison still works, because Excel text comparison ignores case.
For signed amounts, use =IF(D2<0, -D2, 0) for debit and =IF(D2>0, D2, 0) for credit. If amounts are text, wrap them first as VALUE(SUBSTITUTE(D2, ",", "")) before testing the sign.
How do you convert the PDF to begin with?
- If the bank offers CSV or Excel for the period, use that, and skip the PDF.
- Otherwise paste the table into a sheet and use Text to Columns (Excel) or Split text to columns (Google Sheets).
- In Excel 365, Data, Get Data, From File, From PDF can pull in tables directly.
- Delete the account summary, repeated page headings and any total line.
What about dates?
Statements usually print dates day first. If your sheet shows a mix of true dates and text, or the month and day look swapped, rebuild them. For text such as 07/09/2025 in A2: =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)). Sort by the new column afterwards and make sure the dates run in order.
Does the closing balance agree?
Say the opening balance is ₹5,600, total credits ₹28,000 and total debits ₹22,410. Then 5,600 plus 28,000 is 33,600, minus 22,410 is ₹11,190. In an empty cell put =5600+SUM($G$2:$G$500)-SUM($F$2:$F$500) and compare it with the closing balance printed on the statement. If you get ₹11,190 and the statement says the same, the table is complete.
Do the formulas work the same in Google Sheets?
Yes, IF, VALUE, SUBSTITUTE, SUM and DATE behave alike, so everything above carries over. As a second check on the totals when amounts are already numbers, add up by marker: =SUMIF($E$2:$E$500, "Dr", $D$2:$D$500) gives total debits, and the same with "Cr" gives total credits. If those two figures match your split columns, the split was done right. Only locale differs, since a sheet set to another region may read dates the wrong way round.
When is a sheet no longer worth it?
If you do this every month for several accounts, the clean-up becomes the whole job. SpendixAI reads PDF, CSV and Excel files and shows the rows for review before saving, so you can leave the formulas for occasions when you need a custom analysis.
Final checks
- Every amount cell is a number, not text.
- Only one of debit or credit is non-zero on each row.
- The closing balance formula matches the statement.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


