Free Trial

Alteryx Designer Desktop Discussions

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

ALPHANUMERIC STRING FILTER

MulengaYB
6 - Meteoroid

Hi guys,

 

I have a file which has a column that contains alphanumeric data with no unique sequences except that they all have 9 characters. They are not the only data in the cells, therefore I want to extract or filter them out. Below is an example of the data set, the target strings are in bold.

 

OP6CV00111533E:  PAYROLL :- Oct 2024 : A2M4C0111; SUSPENSE: replaced for Orin A/c 444444448 BRN 069 - LD-00443
IPQ8T201117837:Rejected (019) : (45857412571) : FINANCI : boson : X66E90111
IPZE1Q01116981:58HGY0111 Rejected (019) :  (4444568741578)  : S09C0012025010S 
OP7VK6011192E3:PartyOct2024 :  PARTY : Support  : URFYK0111;  replaced for Orin A/c 5555555 BRN 67A - LD-00443
7 REPLIES 7
Raj
16 - Nebula

@MulengaYB 
find the workflow attached
mark done if solved.

MulengaYB
6 - Meteoroid

Hi Raj

 

Thanks for the workflow its almost perfect, the only problem is that its also extracting alphabet string that is 9 letters, the ideal is only to extract alpha numeric that is 9 characters.

OllieClarke
15 - Aurora
15 - Aurora

@MulengaYB 

Try this in the Regex tool in parse mode

\b(?=\w*[A-Za-z])(?=\w*\d)([A-Za-z\d]{9})\b

This uses \b to mark word boundaries so you don't match partial words

It uses 2 positive lookaheads (?=) to ensure there is at least 1 digit and at least 1 letter

and then captures the 9 character string of letters and numbers.

 

Hope that helps,

 

Ollie

MulengaYB
6 - Meteoroid

Hi Ollie

 

Thanks, working perfectly.

flying008
15 - Aurora

Hi, @MulengaYB 

 

You can also parse by :

 

(?:\:\s?)([[:alnum:]]{9})(?=\b)

 

录制_2024_11_15_08_41_23_218.gif

 

 

MulengaYB
6 - Meteoroid

Hi Flying

 

Thanks for the work flow, unfortunately its also capturing alphabets with 9 characters instead of just alpha numeric with 9 characters.

flying008
15 - Aurora

Hi, @MulengaYB 

 

Like @OllieClarke :

\b(?=[[:alnum:]]*[[:alpha:]])(?=[[:alnum:]]*\d)[[:alnum:]]{9})\b
Labels
Top Solution Authors