RegEx

[kas]

Hello,

I struggle with RegEx, but think it is the best option for my need.

The data in the screenshot is all in single field on one row, but I need to break out each line after the colon (:) into their own row.

Current (all in one field)

Desired (each on their own row)

Agent

Yes, greater than 1 year

eCDM,

No

Yes

Life,

Other

Joint owner not added to MM Maintain

Thank you,

Kim

 

[DataNath]

Hey @kas, can you not use Text-to-Columns with colon as a delimiter, setting output columns to 2 and then cleaning up the last column with a Trim() function? Or do you mean you want this in a new row as in your data would now look like:

Requestor

Agent

Was this a processing error?

Yes, greater than 1 year

....

[Raj]

@kas 
use formual tool to achive this
"REGEX_Replace([YourField], ".*?:\s*(.*)", "$1")"

mark done if solved.

find wf attaced for ref.

[kas]

@DataNath 

Hello,

No, I can't use the text to columns, the data in the screen shot are all in one field (not multiple row) on one row.

Thank you

[kas]

@Raj 

Hello,

No, this won't work - the data in the screen shot is all in one field (on one row) not broken out on multiple rows.

Thank you,

[Raj]

@kas 
you can do this way as well

[KGT]

I would use Text to Columns for this. 2 instances of it.

The first one would have delimiter: \n and split to rows. That will give you each row in it's own row. The "\n" delimiter is on newline.

Then use another T2C to split on the colon to 2 fields.

[kas]

@KGT 

This is what I had done, had just hoped for an option with fewer steps if possible since there were multiple instances.

I'll accept this as a solution since it was an option :)

Thank you,

Kim