help cleaning data

[EDO_01_1789]

Good morning,

I solicit your help for a problem that I encounter since several weeks in the cleaning of my data.

In my comment column: several comments that are divided into several lines. Each line corresponds to a comment of a validator.

my problem is that there is no standard in the way people put their comments.

for example sometimes the validation date is at the beginning of the sentence, sometimes in the middle and sometimes at the end.
but what is sure is that the format corresponds each time in the form "yyyy-mm-dd"

so it's very difficult to create a report that looks like this

here is what my comment colum look like :

2022-07-05 15:37:37Z: 1/4, Approve,DA SILVA Menio: 2022-07-05 17:19:35Z: 2/4, Approve,CHANG Cecilia: Customer created 2022-07-05 20:50:30Z: 3/4, Approve,WENDEL Lisa: Betreft samenwerkingsovereenkomst, geen AA. 2022-07-07 10:54:11Z: 4/4, Approve,ETOSSI Samuela:

2022-07-21 15:17:34Z: 1/5, Approve,KELDERMAN Marleen: 2022-07-22 09:33:58Z: 2/5, Approve,WOLBERSEN Ilse: Selectiviteit is verstuurd 2022-07-27 15:15:00Z: 3/5, Approve,CHANG Cecilia: customer created 2022-07-27 15:26:14Z: 4/5, Approve,SPIGHEL Tanguy: 2022-07-27 15:43:01Z: 5/5, Approve,ETOSSI Samuela:

0 2022-08-02 11:58:15Z: Requested by SCHUT Iris 1 2022-08-02 11:59:16Z: Approve,VAN DONGEN Robert-Jan: 2 2022-08-02 16:09:42Z: Approve,CHANG Cecilia: customer created 3 2022-08-02 16:11:06Z: Approve,JBARI Sokaina: 4 2022-08-03 14:10:50Z: Approve,ETOSSI Samuela:

2022-07-01 22:12:24Z:  1: 2022-07-04 09:48:08Z:  2: 2022-07-04 09:51:26Z:  3:

2022-10-17 10:23:18Z:  1: WOLBERSEN Ilse 2022-10-20 16:01:42Z:  2: VAN BELLINGHEN Dominique

2022-07-21 15:17:34Z: 1/5, Approve,KELDERMAN Marleen: 2022-07-22 09:33:58Z: 2/5, Approve,WOLBERSEN Ilse: Selectiviteit is verstuurd 2022-07-27 15:15:00Z: 3/5, Approve,CHANG Cecilia: customer created 2022-07-27 15:26:14Z: 4/5, Approve,SPIGHEL Tanguy: 2022-07-27 15:43:01Z: 5/5, Approve,ETOSSI Samuela:

0 2022-08-02 11:58:15Z: Requested by SCHUT Iris 1 2022-08-02 11:59:16Z: Approve,VAN DONGEN Robert-Jan: 2 2022-08-02 16:09:42Z: Approve,CHANG Cecilia: customer created 3 2022-08-02 16:11:06Z: Approve,JBARI Sokaina: 4 2022-08-03 14:10:50Z: Approve,ETOSSI Samuela:

2022-07-01 22:12:24Z:  1: 2022-07-04 09:48:08Z:  2: 2022-07-04 09:51:26Z:  3:

2022-07-21 15:17:34Z: 1/5, Approve,KELDERMAN Marleen: 2022-07-22 09:33:58Z: 2/5, Approve,WOLBERSEN Ilse: Selectiviteit is verstuurd 2022-07-27 15:15:00Z: 3/5, Approve,CHANG Cecilia: customer created 2022-07-27 15:26:14Z: 4/5, Approve,SPIGHEL Tanguy: 2022-07-27 15:43:01Z: 5/5, Approve,ETOSSI Samuela:

0 2022-08-02 11:58:15Z: Requested by SCHUT Iris 1 2022-08-02 11:59:16Z: Approve,VAN DONGEN Robert-Jan: 2 2022-08-02 16:09:42Z: Approve,CHANG Cecilia: customer created 3 2022-08-02 16:11:06Z: Approve,JBARI Sokaina: 4 2022-08-03 14:10:50Z: Approve,ETOSSI Samuela:

[Anonymous]

Hi @EDO_01_1789 ,

You may try to spilit column with custom text and pivot the rows to columns.

Split a column of text (Power Query) - Microsoft Support

Pivot columns - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.