I created an Excel file to convert PayPay Card transaction statements to CSV

Table of Contents

• Download Excel file
• How to use ① Copy statement
• How to use ② Paste into Excel
• Explanation

Download Excel file

First, please download this Excel file.

Once you open the file, click "Enable Editing".

How to use ① Copy statement

Open the PayPay Card statement screen and copy the table part.

The key is to select and copy from "First store name" to "Last amount".

I was able to copy it without problems in Google Chrome and Edge, but I couldn't select the text properly in Safari or Firefox.

How to use ② Paste into Excel

Paste it into cell "I1eye-one" in Excel.

When you paste it, it will be inserted into "Columns A-G".

Copy this part and use it as you like.

Date in "Column A"

Store name in "Column B"

Many credit card companies have the amount in "Column G", so I've set it up that way.

If there are any irregularities, please let me know in the comments.

Explanation

When you copy the statement and paste it into Column I,

1. Store Name 1
2. Date 1
3. Amount 1
4. Store Name 2
5. Date 2
6. Amount 2

it will be pasted every 3 rows, like this.

Therefore, we use the "INDEX function" to extract data every few rows.

In this case,

Store Name is "=INDEX(I:I,ROW()*3-2)"

Date is "=INDEX(I:I,ROW()*3-1)"

Amount is "=INDEX(I:I,ROW()*3)"

and you can extract it.

These are then placed in Column B, Column A, and Column F respectively.

Here, values like "5,870 yen" entered in Column F are strings, not numbers, so they cannot be calculated as they are.

Therefore, I use the "SUBSTITUTE function" and "VALUE function" to convert it to the number "5870" and place it in "Column G".

Click here for more details on this

If there are any irregularities, please let me know in the comments.

Comments

• #001

  2023-02-13

  Anonymous

  Nice to meet you. I tried to separate the data from the CSV download but couldn't create a good function, and I stumbled upon this by chance.

  By the way, it's a minor thing, but the cell formats in the paste area seem to be inconsistent (lol).

  Also, since the statements are in descending order by date, I reduced the number of items to 50 (rows) and re-referenced them from the bottom in a new column to arrange them in ascending order.

• #002

  2023-02-13

  Kento Kanai

  Thank you for your feedback!

  I'm still not fully proficient with Excel, so I lacked consideration.

  However, as I added at the beginning,
  it seems our efforts have paid off, and the CSV download function has been implemented.