Design Text processor using excel macros

Hi All,

I have a requirement to create a text processor by using MS Excel macros. Please find the below example of chat transcript.

Chat Transcript:
John: 11:36:33 AM
Hi
ServiceDesk : 11:36:34 AM
Hello John !
Thank you for contacting ServiceDesk Agent, please confirm your USER ID(123) by typing “Yes/No”.
*** Please enter 2 to chat in Spanish / Por favor, introduzca 2 para chatear en español ***
John: 11:36:44 AM
yes
ServiceDesk : 11:36:45 AM
If this chat is related to EXISTING TICKET, please type YES. Otherwise,
Please choose option/number for XXX Applications from below list. This list is based on Business Unit from your profile.
Note: If you would like to choose application from other BU, please choose option 8.
(Type the number)
1. XYZ
2. ABC
3. CDE
4. XYZAS 5. JKLKL
6. LKJ 7. ABCUY 8. Go Back (Business Unit Menu)
9. Others
John: 11:37:03 AM
1
ServiceDesk : 11:37:03 AM
You have chosen XYZ Application- Application Link
Note: If you want to choose a different application, please choose option 3 to change application or choose option 4 to change the business unit.
Please select the service option/number from following categories.
(Type the number)
1. Application Access
2. Application Issue
3. Go Back (Application Menu)
4. Go Back (Business Unit Menu)
5. Others
John: 11:37:53 AM
5
ServiceDesk : 11:37:54 AM



Here i am expecting out put should be below after i paste the above data.

Name: John
USER ID: 123
Application : XYZ ( Because John selected option 1 )

Kindly let me know if anybody have similar requirement had earlier.

Thanks
Suresh

Simply put, you are INSANE to try using Excel or VBA for this rather than a scripting language, Python, Javascript, even perl or awk.

John as Name is easy enough to parse out with a regular expression like (\w+): \d\d:\d\d:\d\d [AP]M, and picking off the parenthesized subexpression. USER ID requires storing the proposed user ID in a Service Desk transmission and then looking for a confirmation transmission from the user. Then needing to create an associative array on the fly from a subsequent Service Desk transmission and looking for the index in a subsequent transmission from the user.

Can this be done in Excel? Yes, but it's crude. Here's an example on OneDrive. Download it and run it locally. It's how I implement a text processing state machine in Excel.

Yeah I am asking for help . how can we achieve this .