Challenging VBA Script with Loop, to breakdown text field

[KDavidP1987]

Hello all,

I have a script I am trying to create in VBA for excel, to break out variable portions of a field, then concatenate them together for the result.

Context:
The table contains an extract of survey data, including columns (fields) for the First Name, Last Name, Email Address, and Descriptions

The descriptions field includes all comments left throughout the survey's handling, from beginning to end, including both the user's and responders comments. There is a pattern to this, which should make writing a repeatable script with a loop possible, I just can't figure out how to do so.

I will provide an example of what the aforementioned fields may include in a survey, for reference, and will go over the present patterns below, in case they are not obvious:

First Name: John
Last Name: Smith
Email Addr: John.Smith@Contoso.com
Description:
------------------------------------------------------------------------------------
Entered on 12/26/2018 at 3:15:41 PM EST (GMT-0500) by John Smith:
Hey Kris,


I appreciate you looking into this for me. I hope that the issues will not occur in the future, as this impacts delivery of service to our customers.

Sincerely,
John Smith
VP of Something Important
John.Smith@Contoso.com

Entered on 12/26/2018 at 1:15:41 PM EST (GMT-0500) by Kris Penland:
Hello John,


Thank you for your patience. I have reviewed your ticket and found that there was a miscommunication which caused the problems. I have addressed it with the agents involved.

Sincerely,
Kris Penland




Entered on 12/26/2018 at 1:09:20 PM EST (GMT-0500) by Kris Penland:
Greetings John,

Thank you for filling out our survey, I am researching your issue. I will be in contact soon with a resolution


Entered on 12/19/2018 at 8:41:52 AM EST (GMT-0500) by John.Smith@Contoso.com:




My initial request was closed due to a "miss understanding" of company policy, this
resulted is weeks delay in getting my RSA token fob.
Original Issue = 1234567
Original Project = Service Desk
Original Assignees = A team
dl = ENGLISH
dpr = 12
------------------------------------------------------------------------------------

Patterns:

The description field occurs in descending order by the date (most recent comment at the top)The description always starts (at the bottom), with the user's email address, followed by a : (which signatures in later comments in don't include). The first comment always includes "Original Issue = ########" at the end.

Following the initial comment (At the bottom), which is generated by the survey submission (hence the email address), follow up comments begin to be labeled with the user's name in them (rather than email address), and usually end with their auto-signature, which is company dictated.

Result:
The result should show only the user's comments, without their title included, or the follow up auto-signatures or ticket information which appear proceeding them, and excluding comments from anyone else who responds on the survey. I will likely setup a different field to separate out the agent comments, though currently I just go look them up.

Is this possible??

For reference, this is currently accomplished using the following formulas... but the problem is that they miss anyting following the first 2 user comments.

Comment 1:

=IF([@[<= Neutral]]=1,TRIM(CLEAN( CONCATENATE(
IFERROR(MID([@Comment],SEARCH(CONCATENATE([@[Formatted Date]]," at ",[@[Formatted Time]]," ","???"," (","???","-","????",") by ",[@[Email Address]]),[@Comment])+43+LEN([@[User Email]])+2,SEARCH("Original Issue =",[@Comment])-(SEARCH(CONCATENATE([@[Formatted Date]]," at ",[@[Formatted Time]]," ","???"," (","???","-","????",") by ",[@[Email Address]]),[@Comment])+43+LEN([@[User Email]])+2)),""),
))),"")

Comment P2:

=TRIM(CLEAN(IFERROR(IF(SEARCH([@[Employee Name]],[@Comment]), IFERROR(MID([@Comment],SEARCH("@odfl.com:",[@Comment])+11,SEARCH([@[Employee Name]],[@Comment])-(SEARCH("@odfl.com:",[@Comment])+11)),""),""),"")))

Sincerely,
Kristopher