Need help to split excel cell multiple values and write in single cell

[skyblue1854]

Hi All,

I have an input sheet and one cell can contain multiple values. What is the best way to split them and write it in separate cells?

For example:

This data below is all in one cell:

Note

C8: Monitored/Recorded disclosure statement not provided when another person (applicants wife) took over the call. I2: Agent did not ask the Language needs/preferences question. I6: Applicant was not provided all information that must be included on documents submitted (i.e. identifiers such as full name, DR number, last 4 of SSN, Registration ID). Agent did not tell app to add the last 4 of ssn to documents.

I want to turn it into this:

Note
C8: Monitored/Recorded disclosure statement not provided when another person (applicants wife) took over the call.
I2: Agent did not ask the Language needs/preferences question.
I6: Applicant was not provided all information that must be included on documents submitted (i.e. identifiers such as full name, DR number, last 4 of SSN, Registration ID). Agent did not tell app to add the last 4 of ssn to documents.

Please see attach excel file for full test data. Thanks!

test.xlsx (10.1 KB)

[Yoichi]

Hi,

Can you try the following sample?

mc = System.Text.RegularExpressions.Regex.Matches(strData,"(?<=(^|\n))[A-Z]+\d+:[\s\S]*?(?=\n[A-Z]+\d+:|$)")

Sample20211113-2.zip (10.2 KB)

Regards,

[skyblue1854]

Thank you so much!

It is working as expected:

I made a simple change from this:

(?<=(^|\n))[A-Z]+\d+:[\s\S]*?(?=\n[A-Z]+\d+:|$)

To this:

(?<=(^|\n))[A-Z]+\d+[\s\S]*?(?=\n[A-Z]+\d+|$)

Because inside the test data there is a string that has a period after the initial letter instead of colon:

For example:

C3: Agent did not verify applicant had herd Privacy Act and/or Declaration of Eligibility (Verified/Read). page 68 of RI reference guide
C4. Pre-Populated data provided to caller. Agent provided CPN on file to caller.

It seems to be working after the change.