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 |
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
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:
Hi Philip,
Thanks for your solution but returned null for the date in the second text. How can I write regex for all text?
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.
What's the "second text"?
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. |