About Text With Date

[Inactive User]

I have a table like the below. I want to separate the dates from the sentences in this table. How should I write regex ?

Lorem ipsum dolor sit amet,06-06-1996 consectetur adipiscing elit.

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

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

Lorem ipsum dolor sit28.06.2020 amet, consectetur adipiscing elit.

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

Lorem ipsum dolor sit amet, 25-07-2021consectetur adipiscing elit.

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

I want to seperate like below:

Lorem ipsum dolor sit amet,06-06-1996 consectetur adipiscing elit.	1996-06-06
Lorem ipsum dolor sit amet, consectetur adipiscing elit.20-02-2020	2020-02-20
Lorem2020-16-04 ipsum dolor sit amet, consectetur adipiscing elit.	2020-04-16
Lorem ipsum dolor sit28-06-2020 amet, consectetur adipiscing elit.	2020-06-28
Lorem ipsum dolor sit amet, consectetur12-24-2021 adipiscing elit.	2021-12-24
Lorem ipsum dolor sit amet, 25-07-2021consectetur adipiscing elit.	2021-07-25
Lorem ipsum dolor sit amet,2020-06-14consectetur adipiscing elit.	2020-06-14

[PhilipMannering]

Hi @Inactive User 

You can use,

(\d{2}.\d{2}.\d{4}|\d{4}.\d{2}.\d{2})

and Parse using the Regex Tool.

However this won't format all the dates as yyyy-mm-dd. This is more tricky because your dates are in different formats (in particular mixing the months and days around where there's no discernible way to tell which is which.

Philip 

[Emil_Kos]

Hi @Inactive User,

Like @PhilipMannering the data is in a different format so besides year value nothing is certain  😉

Alternativly you can always clean all unnecessary strings using data cleansing tool and afterwards parse the data using formula tool:

Output:

[Inactive User]

Hi Philip,

Thanks for your solution but returned null for the date in the second text. How can I write regex for all text?

[Emil_Kos]

Hi @Inactive User,

I am not 100% sure but it looks like your data is in 4 different formats. You can check my solution as it doesn't show null for any line. 

[PhilipMannering]

What's the "second text"?

[Inactive User]

If I use the table below as an input, the results come out wrong. How can I write regex for below 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.