Extracting set of values from text

[ToxicBuoy]

Hi,

Thanks for your response in advance.

I have a usecase where I need to extract a set of texts from list of larger cluster of text like below:

Extract all AB, CDE, FG, HIJ, KL from the below column.

1. AB needs to be assigned

2. the work is with CDE

3. FG will assign it to AB, KL to handover process

4. HIJ to confirm AB on role

5. CDE unsure on data accuracy, FG sure on data accuracy

for the above output values should be:

1. AB

2. CDE

3. FG,AB,KL

4. HIJ,AB

5. CDE, FG

[AndrewS]

Hi @ToxicBuoy , the attached should get you what you are looking for?

Spoiler

[ToxicBuoy]

Hi @AndrewS , thanks for responding.

However if my text has another uppercase character, it consumes even that in output, like:

ABC to Submit data To DEF -> ABC,S,T,DEF

Its failing in such cases.

[AndrewS]

Hi @ToxicBuoy - are you able to provide a larger data set to work with? Or will it only be a single capital letter appearing that can be ignored. Also, are you only expecting 2 or 3 upper case letters for any combination?

[AndrewS]

Hi @ToxicBuoy Assuming that you are looking for a 2 or 3 character uppercase combination, change the Regex to 

[A-Z]{2,3}

Updated workflow attached

Cheers,

Andrew

[ToxicBuoy]

Hi @AndrewS , I cant upload any data due to restriction however I am masking the data below:

I am looking for only AB, CDE, FG, KL and HIJ as texts in the 1st column and output as 2nd column.

AB AUTOTAGGED AS to Declare Tax Support Type,CDE to Declare Tax Support Type	AB, CDE
AB to Assign Tax Support Rummy PQS in CP	AB
AB to Assign Tax Support Rummy PQS in CP,AB to Assign Tax Support Rummy PQS in CP	AB,AB
AB to Assign Tax Support Rummy PQS in CP,FG to Assign Tax Support Rummy PQS in CP	AB,FG
AB to Assign Tax Support Rummy PQS in CP,FG to Assign Tax Support Rummy PQS in CP,CDE to Assign Tax Support Rummy PQS in CP	AB,FG,CDE
AB to Assign Tax Support Rummy PQS in CP,FG to Assign Tax Support Rummy PQS in CP,CDE to Submit Removed Request	AB,FG,CDE
None- Pending JZ Score Type Declareation, None- Pending JZ Score Type Declareation	null
None- Pending JZ Score Type Declareation, None- Pending JZ Score Type Declareation,CDE to Submit Tax Notified Request	null
None- Pending JZ Score Type Declareation,AB to Submit Tax Notified Request	null
None- Pending JZ Score Type Declareation,CDE to Submit Tax Notified Request	CDE
KL to Assign Tax Support Rummy PQS in CP	KL

[pedrodrfaria]

Hi @ToxicBuoy 

Please see attached for the WF below:

Pedro.

[AndrewS]

Hi @ToxicBuoy I would try setting the regex to (AB|CDE|FG|KL|HIJ) if you have a determined set of values. Workflow attached. Note your sample has indicated NULL when there is CDE and AB present - is this a typo?

[AndrewS · Accepted solution]

Just realised - make sure Case Insensitive is unchecked on the regex if you go with this approach.

[ToxicBuoy]

Thanks @AndrewS , that logic seems to be working fine. Yes the above was a typo.