Take everything after, including the key

[mihaicom20]

Hello Everyone,

I am trying to return using Regex or something else (text to col is not working) everything after a specific set of keys. When I apply "cinciii(.*)" on Regex is returning everything after "cinciii", but I want to return also "cinciii" and I want to add some more rules in order to not make a regex tool for every key. I have like 6 key that I need to use them :"patruuu, cinciii, sasee, sapteee, opttt, nouaaa" . There is any possibility to include them on a single formula? 

Basically I want to return everything in RED.

Thank you in advance !

NR DOCUMENT CINCIII379296 SC DANIVA B.R.D. - G.S.G.  AG.FILIASI

FACTURA: patruuu348145 B.R.D.#NAME?

FACTURA NR CINCIII380245 ALECCIA STIL FRUTTI 32080725 B.R.D. - G.S.G.  SUC. TITAN BUCURESTI

CIF 14289775 IVAS FRUCT SRL ID FACTURA -CINCIII377398 B.R.D. - G.S.G.  AG. PIATA DOMENII

DEP F nouaaa342121 VANZARI B.R.D. - G.S.G.  AG. REGHIN

PLATA FACTURA FBAT/CINCIII360131 SC MADI BRU COM SRL B.R.D. - G.S.G.  AG. POPESTI-LEORDENI

[ArtApa · Accepted solution]

Hi @mihaicom20 - If you have a list of keys, but don't have a pattern in your data, then this is how you can do this:

[apathetichell · Accepted solution]

You can run regex into a batch macro also to do this...

What I've attached could work for you... Basically you'd run the search term field in the ? anchor as the control field and your datasource into the other end of the macro input. It should search each field for every one of your regex terms and return a column for each of your search terms with the matching entry.

Should being the key word. I haven't tested it for your data but I did change the regex parse..

[AkimasaKajitani · Accepted solution]

Hi @mihaicom20 

If you want to get the Red Letter, you can use this setting.

 - Use Tokenize option

 - RegEx Expression : patruuu.*|cinciii.*|sasee.*|sapteee.*|opttt.*|nouaaa.*

[Jean-Balteryx · Accepted solution]

Hi @mihaicom20,

Adding my 10 cents here using only a the following regex expression :

((?:patruuu|cinciii|sasee|sapteee|opttt|nouaaa).*)

You can also add terms if you need to.

I added the workflow to my post.

[apathetichell]

FYI - if you are only matching one of your keywords at one time in the field - go with @Jean-Balteryx or @AkimasaKajitani  - if you need to match each key word against each entry - the batch macro is a better way.... The one keyword in each field can be done via the | option - but since regex will stop executing once a match is made - this will not work if you need to check each keyword against every record (and hope to get multiple results from each record).

[atcodedog05 · Accepted solution]

Hi @mihaicom20 

My approach would be similar to @ArtApa with slight modifications. I would pick this because in this way to can maintain a list of keywords on a file and use them with no code change.

Workflow:

1. Using select to increase column size.

2. Using formula tool to add "|" to the keyword to create new name. This way you just need to maintain only the list of keyword and not the renames with "|".

3. Using find and replace keywords with words with pipe.

4. Using regex to get only the required based which starts from pipe. Text to column is also a good option.

Hope this helps : )

[Jean-Balteryx]

@mihaicom20 I think you have everything needed to solve your problem !

[mihaicom20]

@ArtApa @Jean-Balteryx @atcodedog05 @apathetichell @AkimasaKajitani  thank you so much everyone.

I already test all of the solutions and work properly. The macro from @apathetichell  it is a little bit more complex and needs some more time to test it on different situations .

Have a great weekend!