How to Convert Bank Statement Text to Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Convert Bank Statement Text to Excel

How to Convert Bank Statement Text to Excel

How to Convert Bank Statement Text to Excel

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.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • Similarly, import Table002 in the same worksheet.
  • Just we’ve to change the output destination.

How to Convert Bank Statement Text to Excel

  • Also, import Table003 in a similar way.
  • Now, the data look as shown in the below image.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • Instantly, a Microsoft Excel warning box appears.
  • Now, click OK on the box.

How to Convert Bank Statement Text to Excel

  • Presently, we can clearly notice that the filter buttons got vanished from the header row. And, they got transformed into a simple data range.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • At present, the statement looks like the image below.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

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.

How to Convert Bank Statement Text to Excel

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.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • 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.

How to Convert Bank Statement Text to Excel

  • Though we select cell B4, the imported data occupies the sheet from the top left portion.

How to Convert Bank Statement Text to Excel

  • Then, select cell A1.
  • After that, go to the Table Design tab.
  • Later, select the Convert to Range tool on the Tools group.

How to Convert Bank Statement Text to Excel

  • Presently, click OK on the warning box.

How to Convert Bank Statement Text to Excel

  • In this instance, delete the first 3 rows.
  • Then, select cell A2 and give a suitable title to the dataset.

Giving Suitable Title to Dataset

  • Subsequently, remove all the borders and fill colors from the whole worksheet.

Removing Formatting to Give a Clean Look

  • 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.

Applying Formatting to Give a Lucrative Look

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.

Converting CSV File to Spreadsheet

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.

Converting txt File to Spreadsheet

  • 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.

Converting txt File to Spreadsheet

  • 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.

Converting txt File to Spreadsheet

  • At present, the imported data table looks like the one below.

Converting txt File to Spreadsheet

  • 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.

Converting txt File to Spreadsheet

  • Correspondingly, remove all the borders and fill colors from the whole worksheet.
  • Thus, the worksheet looks like the image below.

Converting txt File to Spreadsheet

  • 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.

Converting txt File to Spreadsheet

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.


Related Articles

Shahriar Abrar Rafid

Shahriar Abrar Rafid

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo