Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Take everything after, including the key

mihaicom20
7 - Meteor

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

8 REPLIES 8
ArtApa
Alteryx
Alteryx

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:

ArtApa_0-1627000084936.png

 

apathetichell
15 - Aurora

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
14 - Magnetar

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.*

 

AkimasaKajitani_0-1627018858258.png

 

 

Jean-Balteryx
14 - Magnetar

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
15 - Aurora

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
18 - Pollux

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:

atcodedog05_1-1627049190154.png

 

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
14 - Magnetar

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

mihaicom20
7 - Meteor

@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!

Labels