ALPHANUMERIC STRING FILTER

[MulengaYB]

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

[Raj]

@MulengaYB 
find the workflow attached
mark done if solved.

[MulengaYB]

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 · Accepted solution]

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

Hi Ollie

Thanks, working perfectly.

[flying008]

Hi, @MulengaYB 

You can also parse by :

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

[MulengaYB]

Hi Flying

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

[flying008]

Hi, @MulengaYB 

Like @OllieClarke :

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