Regular expression: Regex Tokenize

[Chirag_KRCPL]

Given below is the string:

;2;64;2;5;06/11/2002;3;1.000000000;5;08/26/2016;3;.991758242;5;11/26/2016;3;.983516484;5;02/26/2017;3;.975274725;5;05/26/2017;3;.967030000;5;08/26/2017;3;.958791209;5;11/26/2017;3;.950549451;5;02/26/2018;3;.942307692;5;05/26/2018;3;.934065934;5;08/26/2018;3;.920329670;5;11/26/2018;3;.906593407;5;02/26/2019;3;.892857143;5;05/26/2019;3;.879120879;5;08/26/2019;3;.865384615;5;11/26/2019;3;.851648352;5;02/26/2020;3;.837912088;5;05/26/2020;3;.824175824;5;08/26/2020;3;.810439560;5;11/26/2020;3;.796703297;5;02/26/2021;3;.782967033;5;05/26/2021;3;.769230769;5;08/26/2021;3;.755494505;5;11/26/2021;3;.741758242;5;02/26/2022;3;.728021978;5;05/26/2022;3;.714285714;5;08/26/2022;3;.686813119;5;11/26/2022;3;.659340659;5;02/26/2023;3;.631868132;5;05/26/2023;3;.604395604;5;08/26/2023;3;.576923077;5;11/26/2023;3;.549450549;5;02/26/2024;3;.521978022;5;05/26/2024;3;.494505495;5;08/26/2024;3;.469780220;5

As few sample patterns highlighted in red, the pattern that I want to extract is "Date, 3 & number immediately after 3" (notice that date is always immediately before 3)

I want the data to be splitted in rows.

Can someone please help me here?

Regards  

[Yoshiro_Fujimori]

@Chirag_KRCPL ,

Honestly I am not good at RegEx. So I used Text To Column as one of the solutions.

Does it work for you?

[ShankerV]

Hi @Chirag_KRCPL 

One way of doing this using Regex is.

(\d{2}\/\d{2}\/\d{4};3;[^;]*)

Many thanks

Shanker V

[ShankerV]

Hi @Chirag_KRCPL 

One more simplified regex expression also can be used as below.

(.{10};3;[^;]*)

Many thanks

Shanker V

[Yoshiro_Fujimori · Accepted solution]

@Chirag_KRCPL ,

I managed to solve it using RegEx.

The point is to prepare a new separater (pipe "|") for the next Text To Column tool.

[Yoshiro_Fujimori]

@ShankerV ,

Thank you for sharing this.

The use of [^;]* is cool !👏

[Chirag_KRCPL]

Hi @ShankerV  many thanks for this solution.

I have understood the entire expression except usage of [^;]* in your expression (\d{2}\/\d{2}\/\d{4};3;[^;]*)

Can you please take a moment to explain this expression.

Many thanks in advance!

[Yoshiro_Fujimori · Accepted solution]

@Chirag_KRCPL ,

The pattern [^;]* means

More than 0 occurrence of the pattern inside of [ ],

which is "any character not semi-colon".

With this expression at the end of the pattern, it will include all the characters until it finds the next semi-colon.

As a result, [^;]* works to set semi-colon as a separator.

I hope this answers to your question.

Thanks again @ShankerV !

[ShankerV · Accepted solution]

Hi @Chirag_KRCPL 

@Yoshiro_Fujimori has explained the usage of [^;]*

Thanks @Yoshiro_Fujimori 

[Chirag_KRCPL]

@Yoshiro_Fujimori @ShankerV , many thanks for your help!!