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 |
Solved! Go to Solution.
@MulengaYB
find the workflow attached
mark done if solved.
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.
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
Hi Ollie
Thanks, working perfectly.
Hi Flying
Thanks for the work flow, unfortunately its also capturing alphabets with 9 characters instead of just alpha numeric with 9 characters.