excel vba to separate single column entries into database format

thank you for your reply...

here's an excerpt of the original text file...

the length of each message is random...the only regularly occurring delimiters are as I previously mentioned...

also, another regular occurrence is that there are two lines that distinguish each message...

text sample.txt





hi...I'm a completely a newcomer to excel vba...only used couple of functions for my work...

until my boss threw me with a text file with 100K lines...and the text-to-columns function won't be of any help...

I have been searching for hours to find a vba solution for my problem to no avail...

the format of the text file goes like this....


Date : xx.xx.xxxx From : John doe To : Jane Doe, Chris Doe, etc.....
Contents : blah blah blah...
blah blah something...


Date : xx.xx.xxxx From : John doe, Jane Doe To : Chris Doe, Jack, Amy, etc.....
Contents : blah blah blah...
blah blah something...

the length of the sender and recipient almost always varies...and the length of the contents is also inconsistent...
no ratio or length limits can be applied(as I need to pull out all of the contents in one cell...)
moreover the contents itself can be separated by two or more lines(rows) on a random occasion and there may be the words
"Date", "From", "To" within the content itself....

what I am trying to do is convert the text file into an excel database form file...with the row headers Date / From / To / Contents...
allowing no empty rows or cell entries(so that the database can be auto-filtered...)

I think the only so-called delimiters(or separators) in the text file would be "Date :", "From :", "To :", "Contents :"

But I can't seem to find any codes which fit my purpose...

Any help would by so much appreciated....

Thank you in advance....

It will be good if you post a sample excel file here.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Should the output be an excel file or a .txt file?