About Text with Date

[Inactive User]

I have a table like below. How can I separate the dates from the sentences in this table?

Lorem ipsum dolor sit amet17-05-2006consectetur adipiscing elit.

Lorem ipsum dolor sit amet, consectetur adipiscing elit.20-2-2020

Lorem2020-16-04ipsum dolor sit amet, consectetur adipiscing elit.

Lorem ipsum dolor sit28-06-2020amet, consectetur adipiscing elit.

Lorem ipsum dolor sit amet consectetur12-24-2021adipiscing elit.

Lorem ipsum dolor sit amet25-07-2021consecteturadipiscing elit.

Lorem ipsum dolor sit amet2020-06-14consectetur adipiscing elit.

I want to seperate like below :

Lorem ipsum dolor sit amet17-05-2006consectetur adipiscing elit.	2006-05-17
Lorem ipsum dolor sit amet, consectetur adipiscing elit.20-2-2020	2020-02-20
Lorem2020-16-04ipsum dolor sit amet, consectetur adipiscing elit.	2020-04-16
Lorem ipsum dolor sit28-06-2020amet, consectetur adipiscing elit.	2020-06-28
Lorem ipsum dolor sit amet consectetur12-24-2021adipiscing elit.	2021-12-24
Lorem ipsum dolor sit amet25-07-2021consecteturadipiscing elit.	2021-07-25
Lorem ipsum dolor sit amet2020-06-14consectetur adipiscing elit.	2020-06-14

[Emil_Kos · Accepted solution]

Hi @Inactive User,

I have created a new solution.

First I am using regex in order to keep only numbers dashes. Later I have parsed the data using the formula:

Please mark this post as a solution if helpful.

[Inactive User]

Thanks a lot @Emil_Kos

[Emil_Kos]

Hi @Inactive User,

if this was helpful for you please mark my post as a solution.

by the way this second row wasn’t working because it was written in different format then rest of the data.

[ImadZidan]

Hello @Inactive User , 

@Emil_Kos solution is brilliant.

One thing to point out for the sake of knowledge. Datacleansing tool can also be used to extract the dates.

The rest of the Emil's workflow should stay the same. See cleansingtool attached.

I guess your challenge is getting the dates correct. In case of 2020-16-04, it is obvious that it is 16th of April. However, if that was 2020-11-04, will it then be 11 of April or 04th of November. 

Good luck.