RegEx

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)

Extended Comments.jpg

Desired (each on their own row)

Agent

Yes, greater than 1 year

eCDM,

Yes

Life,

Other

Joint owner not added to MM Maintain

Thank you,

Kim

Regex

Preparation

Accepted answers

All comments

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.

7135.yxmd

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

7135.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703