Break a bank statement PDF into multiple files

[BrianHoj]

We have a situation where we are trying to turn PDFs of bank statements into Excel Files.  The structure of the file is such that there is header information (date, acct number, page #, etc..), then sections with a title row such as "Deposits and Additions", "Checks and Withdrawals", "Checks in Serial Number Order", and "Daily Balance Info".  Each section is structured into nicely laid out columns with headers, but the sections have different column layouts.  The number of rows in each section are variable based on the number of transactions.  I have been able to turn the PDFs into Text files.  But where I am getting stuck is trying to extract the different sections and then combining into a table.  Has anyone been able to do something similar where you search through a text file for a phrase and then grab a variable amount of rows that occur before the next phrase?  If needed I can put a sample file up for review.

Thanks in advance.

[JohnJPS]

The attached workflow uses a multirow formula to assign a "section" value parsed from dummy data... perhaps you can do something similar with your file.  If not, please do post your sample file.  Thanks!

[BrianHoj]

John,

It looks like your workflow may get me going.  I've attached two sample documents.  The first is the bank statement in TXT format and the second is the resulting Spreadsheet we are looking to produce.

[JohnJPS · Accepted solution]

I modified the workflow to get a bit closer. There's a lot of conditional logic within formulas to manually parse things out using SubString.  If anyone else can think of something lass "brute force" and more elegant, have at it.  But this will work if you flesh it out using the same technique, for anything I may have missed.  Hope that helps!

[BrianHoj]

John, that is super helpful.  Indeed that is what I was looking for.  Thank you so much for the assistance.  I can take it from here.

---

@JohnJPS wrote:

I modified the workflow to get a bit closer. There's a lot of conditional logic within formulas to manually parse things out using SubString.  If anyone else can think of something lass "brute force" and more elegant, have at it.  But this will work if you flesh it out using the same technique, for anything I may have missed.  Hope that helps!

---

Thanks again!