extract substring after certain word(s)

[Drew502]

I have a long string where I am looking to extract two values: 

1: The numeric value that appears after "fullAcctNumber"

2. The string (can be multiple words) that appears after "Instrument, paramVal=". 

As in the attached example, there can be multiple instances within the same string. I need to extract all instances within the string

ideal output (can be in multiple columns or rows):

"123456789:Individual"

"987654321:IRA"

string example:

acctType=INV, extAcctType=INV, acctNumber=123456789), AcctName(paramName=NickName, paramVal=Bobs Brokerage)], acctOwnerName=null, fullAcctNumber=123456789), acctDateTime=[AcctDateTime(dateTimeType=LastUpdateAttempt, dateTimeVal=2023-09-09), property=[Property(paramName=RetirementStatus, paramVal=N R), Property(paramName=Instrument, paramVal=Individual), Property(paramName=AccountOwnership, paramVal=Individual)]), acctType=INV, extAcctType=INV, acctNumber=987654321), AcctName(paramName=NickName, paramVal=Bobs IRA), acctOwnerName=null, fullAcctNumber=987654321), acctDateTime=[AcctDateTime(dateTimeType=LastUpdateAttempt, dateTimeVal=2023-09-09), property=[Property(paramName=RetirementStatus, paramVal=R), Property(paramName=Instrument, paramVal=IRA), Property(paramName=AccountOwnership, paramVal=Individual)])])]))

[FinnCharlton · Accepted solution]

Hi @Drew502 , you can do this with the Tokenize option of the Regex tool:

[Drew502]

@FinnCharlton This is great!! Huge help!! Thank you.

For the below section of the expression, how could we modify it to look for multiple words? It looks like it is stopping when there is a space. The values commonly have spaces in them (ex: Rollover IRA, Roth IRA, Brokerage, Joint, Trust, etc). If it helps, there will always be a closed parenthesis at the end of the string.

Instrument, paramVal=\w+

[FinnCharlton]

@Drew502 very good point, you can tell it to take everything it finds before the bracket, like this:

Instrument, paramVal=[^\)]+

[Drew502]

@FinnCharlton Thanks again for your help on this! Unfortunately my data has changed and the reg ex broke. I've tried playing around with it but I have a feeling the quotes are what is throwing me off.

As a reminder, I have a giant string that I am trying to return 2 values

1: The numeric value that appears after "FullAcctNumber\" : \"  (note: the number of astrix can vary)

2. The string (can be multiple words) that appears after Instrument\",\n \"ParamVal\" : \"

There can be multiple instances of both values

Example:
"FullAcctNumber\" : \"*****1234"  .................................    Instrument\",\n \"ParamVal\" : \"Roth IRA\"

[FinnCharlton]

Hey @Drew502, try this Regex with the Tokenize option:

"FullAcctNumber\\" : \\"\d+?"|Instrument\\",\\n \\"ParamVal\\" : \\".+?\\"