Extract text from string

[tww]

Hi all,

I need some help with text extraction. See the example below, I have a free text field and I want to extract only the values associated with (uid:VEN).

Input

Chief Risk Officer (uid: ELT.015);Risk Management (uid: ORG.011);Risk Management - BSA (uid: ORG.011.002);Bank Secrecy Act (BSA) and OFAC Sanctions (uid: REG.005);0-Not Applicable (uid: VEN.N/A);0-Not Applicable (uid: APP.N/A);0-Not Applicable (uid: MOD.N/A)

Risk Management (uid: ORG.011);Risk Management - BSA (uid: ORG.011.002);Bank Secrecy Act (BSA) and OFAC Sanctions (uid: REG.005);0-Not Applicable (uid: VEN.01A);0-Not Applicable (uid: APP.N/A);0-Not Applicable (uid: MOD.N/A)

3-Not Applicable (uid: VEN.N07);0-Not Applicable (uid: APP.N/A);0-Not Applicable (uid: MOD.N/A)

Risk Management (uid: ORG.011);Risk Management - BSA (uid: ORG.011.002);0-Not Applicable (uid: APP.N/A);4-Not Applicable (uid: VEN.40);;0-Not Applicable (uid: MOD.N/A); Chief Risk Officer (uid: ELT.018)

Output

0-Not Applicable (uid: VEN.N/A)

0-Not Applicable (uid: VEN.01A)

3-Not Applicable (uid: VEN.N07)

4-Not Applicable (uid: VEN.40)

Thank you in advance for your assistance.

TWW

[ChrisTX]

For the first segment you want to extract, is the template always a number then a dash then Not Applicable?

Providing only 4 examples, if they don't accurately represent the full population, can result in inaccurate solutions.

Chris

[ChrisTX]

Try the RegEx tool in Replace mode with this expression

.*(\d-Not Applicable).*(\s\(uid: VEN\..*?\)).*

replace with $1$2

Chris

[tww]

Hi ChrisTX,

Thank you for the quick response. The text varies, could be combination of any alphanumeric characters. The only identifier is "uid: VEN".

TWW

[ChrisTX · Accepted solution]

Without more examples, I'm just guessing

Try the RegEx tool in Replace mode or Parse mode with this expression

.*;*(\d.*\s\(uid:\sVEN.*?\))

If using Replace mode, replace with $1

Chris