Cards and UPI

Convert a credit card statement PDF to Excel without retyping

Convert a credit card statement PDF to Excel without retyping: unlock the file, import the rows, fix split merchant names and credit entries, then clean the columns.

Editorial illustration for Convert a credit card statement PDF to Excel without retyping

To convert a credit card statement PDF to Excel, unlock the PDF with its password, bring the table in with your spreadsheet's PDF import or by copy and paste, then clean the columns: join split merchant names, turn the amounts into real numbers, and mark credits as negative. Expect an hour of tidying the first time and much less afterwards.

Card statements are harder than bank statements because of multi-line merchant names, credit entries marked with Cr and several tables on one page. These steps handle each of them.

Step 1: unlock the PDF and look at its layout

Open the file with the password from your issuer's email and check whether you can select the text with your cursor. If you can, the PDF holds real text and converts well. If the page behaves like a picture, you would need text recognition, and the result needs far more checking. Better still, ask your issuer whether a CSV or Excel statement is available.

Step 2: bring the rows into a sheet

  • Excel (recent Windows versions): Data, Get Data, From File, From PDF, then pick the table that holds your transactions. Availability depends on your version, so check your menu.
  • Google Sheets: there is no direct PDF import, so open the PDF, select the table, copy, and paste into a sheet.
  • Either way, paste into a scratch sheet and keep the original untouched.

Step 3: fix multi-line merchant names

When a merchant name wraps onto a second line, the second line often becomes its own row with no date and no amount. Suppose dates are in column A and descriptions in column B, starting in row 2. In a helper column F, enter this in F2 and fill down:

=IF(A3="", B2&" "&B3, B2)

It joins a row with the one below when the next row has no date. Then delete the continuation rows. This handles names that wrap onto two lines; a third line needs the same step repeated.

Step 4: turn text amounts into numbers

Amounts often arrive as text with commas, and credits carry the letters Cr after them. Suppose the amount text is in D2. This formula makes debits positive and credits negative, in a new column G:

=IF(RIGHT(TRIM(D2),2)="Cr", -VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(D2,"Cr",""),",",""))), VALUE(TRIM(SUBSTITUTE(D2,",",""))))

Check a few results by hand. If VALUE returns an error, the cell may hold a hidden character or a different decimal style, so retype one amount and compare.

Raw text in the PDFAfter cleaningWhy
1,250.001250Comma removed, text turned into a number
4,800.00 Cr-4800A refund or payment, shown as a credit
SWIGGY BANGALORE INOne row, one merchantName rejoined after a line break

Step 5: sanity-check against the statement

Sum the cleaned column and compare it with the statement's totals. Payments and refunds make a negative number, so check that new purchases minus credits matches what the summary says. If it is out by exactly one amount, you have a missing or doubled row. If the difference is not a single row's amount, look at the page breaks, where rows are most often lost.

When a sheet stops being enough

If you do this every month, the cleaning becomes the job. SpendixAI accepts card statements as PDF, CSV or Excel: you pick the card account first, upload, and review the rows before saving, and corrections you make become history. Not every layout parses perfectly, so keep checking the totals.

Common problems after conversion

  • Dates arrive as text. If a date such as 15/03/2024 sits in A2 as text, this formula builds a real date: =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)). It assumes day, month, year order, so check yours.
  • Header rows repeat on every page. Filter out the rows where the date column says Date, then delete them.
  • Two columns merge into one. Use Data, Text to Columns in Excel, or Split text to columns in Google Sheets.
  • Page totals and summary boxes land in the middle of the table. Delete them before totalling.

A quick end-of-conversion checklist

  1. Every row has a date, a merchant and a number.
  2. Credits are negative.
  3. The total matches the statement summary.
  4. The card name is noted on every row, in case you combine statements.
SPENDIXAI JOURNAL

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

Explore your reports