Shahriar Abrar Rafid
Need to learn how to convert bank statement text to Excel? We are all aware of the significance of bank statements from both a private and professional standpoint. So, it is essential for us to keep track of our monthly expenditures and incomes. If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 3 easy and convenient ways of converting bank statement text to Excel.
Download Practice Files
You may download the following Excel workbook, CSV, and text file for better understanding and practice yourself.
3 Ways to Convert Bank Statement Text to Excel
However, as is common knowledge, the majority of banks send their statement in PDF format, which makes copying them difficult without a PDF application. To properly copy and alter the information, people choose to convert the bank statements to Excel format. Let’s see how we do that with different methods.
1. Convert Bank Statement Text from PDF to Excel
In this case, we have a bank statement for June’22 in PDF format.
Now, we’ll convert this bank statement into an Excel spreadsheet. So, without further delay, let’s go through the approach step by step.
📌 Steps
- First of all, open a new workbook.
- Secondly, go to the Data tab.
- Thirdly, click on the Get Data drop-down on the Get & Transform Data group.
- After that, tap the From File sub-menu.
- Then, choose From PDF from the available options.
- Suddenly, the Import Data window will open up.
- Then, select the correct file path where the PDF file is stored.
- Afterward, choose the right file. Here, we’ve selected Bank Statement-June.
- Later, click on the Open button.
- At this moment, the Navigator window opens.
- Now, make sure to check the box of Select multiple items.
- Then, check three boxes containing tables (Table001, Table002, and Table003).
- Lastly, click the Load button.
- At present, we’re on the Queries & Connections task pane. It’ll be available on the right of your display.
- At first, right-click on Table001 (Page 1).
- Secondly, click on Load To from the context menu.
- Instantly, the Import Data dialog box opens.
- At this point, choose Table in the Select how you want to view this data in your workbook option.
- Then, select the Existing worksheet and B4 cell as our output destination in the Where do you want to put the data? Option.
- Similarly, import Table002 in the same worksheet.
- Just we’ve to change the output destination.
- Also, import Table003 in a similar way.
- Now, the data look as shown in the below image.
- They are in a Table format now. We’ll convert them into a normal data range.
- Firstly, select any cell on the table. In this situation, we select cell B4.
- Then, the Table Design tab appears.
- After that, select the Convert to Range tool on the Tools group.
- Instantly, a Microsoft Excel warning box appears.
- Now, click OK on the box.
- Presently, we can clearly notice that the filter buttons got vanished from the header row. And, they got transformed into a simple data range.
- At this moment, select the whole dataset.
- After that, move to the Home tab.
- Then, select the Fill Color drop-down.
- Later, choose No Fill to apply to the entire dataset.
- At present, the statement looks like the image below.
- Finally, give a suitable title to the dataset. As we have given Converted Bank Statement in Excel from PDF.
- Also, do the necessary formatting like applying borders and filling cells with colors.
- Lastly, our bank statement gets the right outlook to be an Excel sheet.
Read More: Bank Reconciliation Statement in Excel Format
2. Convert Bank Statement Text from CSV File to Excel
In this method, we have a bank statement for January’22 in CSV file format.
Here, we can see that the entities are separated from one another with a comma as the delimiter. Let’s describe the method with some easy steps.
📌 Steps
- At the very beginning, select cell B4.
- Afterward, go to the Data tab.
- Later, select From Text/CSV on the Get & Transform Data group.
- Immediately, the Import Data window opens.
- Now, choose the right file directory.
- Then, select the correct file from the list.
- Lastly, click on the Import button.
- Then, a preview window opens.
- At this moment, set Comma as the Delimiter.
- Also, we can see the data preview in the image below.
- Later, select the Load button.
- Though we select cell B4, the imported data occupies the sheet from the top left portion.
- Then, select cell A1.
- After that, go to the Table Design tab.
- Later, select the Convert to Range tool on the Tools group.
- Presently, click OK on the warning box.
- In this instance, delete the first 3 rows.
- Then, select cell A2 and give a suitable title to the dataset.
- Subsequently, remove all the borders and fill colors from the whole worksheet.
- Afterward, do the necessary formatting like applying borders and filling cells with colors.
- Lastly, our bank statement gets the right outlook to be an Excel sheet.
Read More: How to Edit Bank Statement in Excel (with Easy Steps)
3. Convert Bank Statement Text from Text File to Excel
Converting bank statement text from a text file to Excel is kind of similar to our previous method. Here, we’ve got a bank statement for January’22 in text file format.
Here, we can see that the entities are separated from one another with a tab as the delimiter. Let’s describe the approach step by step.
📌 Steps
- Initially, select cell B4.
- Then, proceed to the Data tab.
- Later, select From Text/CSV on the Get & Transform Data group.
- Immediately, the Import Data window opens.
- Now, choose the right file directory.
- Then, select the correct file from the list. Here, we chose Bank Statement Text from the list.
- Lastly, click on the Import button.
- Then, a preview window opens.
- At this moment, set Tab as the Delimiter.
- Also, we can see the data preview in the image below.
- Later, select the Load button.
- At present, the imported data table looks like the one below.
- Afterward, convert the table into a simple data range as we’ve shown in the second method.
- Also, give an appropriate title to the dataset.
- Correspondingly, remove all the borders and fill colors from the whole worksheet.
- Thus, the worksheet looks like the image below.
- After that, do the necessary formatting like applying borders and filling cells with colors.
- Ultimately, our bank statement gets the right outlook to be an Excel sheet.
Read More: How to Maintain Accounts in Excel Sheet Format (4 Templates)
Practice Section
For doing practice by yourself, we have provided the CSV and Text file along with the workbook. Don’t forget to download the Practice files. Please do it by yourself.
Conclusion
This article provides easy and brief solutions to convert a bank statement to an Excel spreadsheet. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.
