Challenging VBA Script with Loop, to breakdown text field

KDavidP1987

Board Regular
Joined
Mar 6, 2018
Messages
51
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?? :confused:

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:

Code:
=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:

Code:
=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
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top