I have emails coming to my inbox on a daily basis. Starts with notification wording (approximately one or two paragraphs). Then goes table in a text format. Then goes another paragraph or two.

I connected through Microsoft Exchange to this folder. When consolidate, I need to remove all the data and hopefully can consolidate all text tables together (need monthly data) and then split them (text to columns or column split). Tried using Power Query, not working well. Cannot do it in consistent steps.

Can anyone advise what could be other options? Thanks!

The email looks like below:

From: Bank
Sent: Thursday, April 12, 2017 8:52 AM
To:
Subject: Payment Notification


Payment Receipt Advice


Report Date: 12-Apr-17


Originator Receiver
ABC Company XYZ Companny



Contact
Email address:


Direct Responses to:



Payor: ABC Companny
Payment Date: 11-Apr-17
Payment Amt: $100





Code Reference Date Total Amt/ Amount Paid/ Discount/


Credit Note Number 91A 09-Apr-17 -100 -100 0.0
Invoice Number 38C 28-Feb-17 200 200 0.00

Totals: 100 100 0.00
_______________________________________________________________________
The purpose of this communication is to notify the Receiver of an intended payment by
the
Originator. Failure to receive the Payment Amount on the Payment Date should be communicated by
the
Receiver to the Originator, who is responsible for the payment of such amount.